PDA

View Full Version : combining row data to make a label



twentyeight7
09-02-2008, 06:54 AM
I have a list of wires run in a home and I would like to automate the process of combining the data to make a wire number. the sample shows one room's data. i have the room number(008A) and wire type(Cat6-Green). this should be combined as 008A-CAT6G-1 wire number should be sequential by type and room number. wires should only be counted if they are used. spare wire's labels disregard type and label them sequentially as "008A-SPR-1"

i have made a few formulas already(these are in my sample sheet):

1.) i have a column that i mark with "x" if the wire is to be used. this sends it through a if that will either combine wire type and room number or count it as a spare
2.) to convert the full wire type "CAT6-Green" to the shorthand "CAT6G".
3.) combine the room number with the wire type resulting in "008A-CAT6G"

so all that is left is to count and label the numbers, by type, by room. can any one point me in the right direction

Bob Phillips
09-02-2008, 08:47 AM
=IF(A1="X",C1&"-"&B1&"-",C1&"-SPR-")&COUNTIF($B$1:B1,B1)

twentyeight7
09-02-2008, 09:11 AM
thanks for the quick reply. i did a small tweak so that i could have seprate numbers for spare or not (i filled column E with SPR)

=IF(A57="x",C57&"-"&B57&"-",C57&"-SPR-")&IF(A57="x",COUNTIF($B$1:B57,B57),COUNTIF($E$1:E57,E57))

now the only thing that is left is that the numbers have to reset for each room. , how could i do this?


Thanks Alot!

Chris

Bob Phillips
09-02-2008, 09:18 AM
You can do that more simple

=IF(A1="x",C1&"-"&B1&"-"&COUNTIF($B$1:B1,B1),C1&"-SPR-"&COUNTIF($E$1:E1,E1))

although I admit I don't get it as it always retunr 0 for SPR for me.

I don't understand the other bit.

Bob Phillips
09-02-2008, 09:20 AM
thanks for the quick reply. i did a small tweak so that i could have seprate numbers for spare or not (i filled column E with SPR)

=IF(A57="x",C57&"-"&B57&"-",C57&"-SPR-")&IF(A57="x",COUNTIF($B$1:B57,B57),COUNTIF($E$1:E57,E57))

now the only thing that is left is that the numbers have to reset for each room. , how could i do this?


Thanks Alot!

Chris

Actually, is that right? I get 2 as the suffix for LAN. Whilst there are 2 LANs, only one has x in column A.

twentyeight7
09-02-2008, 09:30 AM
when that numbers it, it puts the number of the order that it is in. so if the first lan did not have a "x" and the second does the second will still be labeled 2. this is fine with me because it will stop numbers from changing on updates

as for the other bit..... i want all the numbers to reset for every room i have on the list(the sample only has one)

Bob Phillips
09-02-2008, 09:43 AM
Create a sample with more than one and show what you mean, if necessary type the results in.

twentyeight7
09-02-2008, 09:52 AM
i think i got it. i just need to adjust the range

Room 1 =IF(A19="x",C19&"-"&B19&"-",C19&"-SPR-")&IF(A19="x",COUNTIF($B$1:B19,B19),COUNTIF($D$1:D19,D19)
Room 2 IF(A57="x",C57&"-"&B57&"-",C57&"-SPR-")&IF(A57="x",COUNTIF($B$57:B57,B57),COUNTIF($D$57:D57,D57))

is there anyway i can automate this?

Bob Phillips
09-02-2008, 09:56 AM
Automate, in what respect?

You do not need to repeat the test for x as I showed earlier.

twentyeight7
09-02-2008, 10:04 AM
attached is the sample

notice how all the numbers reset at 41 when there is a new room. how can i get the formula to see the change in room number and set the new range