For all rows, if cell has "X", get number from range?

[Ed from AZ]

I'm hoping this can be done with a formula - I'd like to stay away
from macros unless I have to.

This is a job order tracker. Each job order entry takes four rows.
The first few columns are job info; the next 31 columns are the dates
of the month. The first row of each job order is for days worked - if
that job was worked on that day, an "X" goes in the row under that
date.

I'd like to put a formula at the end of each job order that captures
all the dates the job was worked, as indicated by the "X" in that
column in that job order.

Something like this:

1 2 3 4 5
Job#1 Worked X X
Units
Lots
Shipped
Job#2 Worked X X X
Units
Lots
Shipped
Job#3 Worked X X X
Units
Lots
Shipped

So Job#1 would show dates worked as 1, 2; Job#2 as 2, 3, 4; Job#3 as
3, 4, 5.

Is there a formula that will do this?
Ed

 

[Bernard Liengme]

If you can live without the commas:
Assuming the 1,2,3,4,5 are in row 1, and the first Job is in row 2
In H2 enter
=IF(B2="X",$B$1,"")&IF(C2="X",$C$1,"")&IF(D2="X",$D$1,"")&IF(E2="X",$E$1,"")&IF(F2="X",$F$1,"")
Select H2:H5 and copy this down the column by dragging the fill handle
(small solid square in lower right corner of H5 when you have selected
H2:H5)


If your really need the commas, here is a start
=IF(COUNTA(B2),1,"")&IF(COUNTA(B2:C2)=2,",2",IF(COUNTA(B2:C2)=1,"2",""))

But a VBA function would be reasonable simple
best wishes

 

[Ed from AZ]

Thank you, Bernard. Yah, the commas are going to be pretty important,
so I'd better go with the second set up. Can I "&" 31 IF
statements?? Wonder what the recalc time is down 100-ish rows??

A UDF would be much cleaner, but I have people who don't know what to
do with a macro warning (they think a virus is trying to kill them!)
and others who simply don't know how or don't bother to turn macros
on.

I appreciate the boost.
Ed

 

[Bernard Liengme]

I am not sure there is a limit on the number of terms in a concatenation
formula since a&b&c is not nested
The IF is going to get messy with 5 columns, I hope you do not have 31 of
them!!

If you can live with results such as 2,,4,5, rather than 2,4,5 then you
could just alter my first formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Thank you, Bernard. Yah, the commas are going to be pretty important,
so I'd better go with the second set up. Can I "&" 31 IF
statements?? Wonder what the recalc time is down 100-ish rows??

A UDF would be much cleaner, but I have people who don't know what to
do with a macro warning (they think a virus is trying to kill them!)
and others who simply don't know how or don't bother to turn macros
on.

I appreciate the boost.
Ed

 

[Alexander Wolff]

Die Tastatur von Ed from AZ wurde wie folgt gedrückt:

Something like this:

1 2 3 4 5
Job#1 Worked X X
Job#2 Worked X X X
Job#3 Worked X X X

So Job#1 would show dates worked as 1, 2; Job#2 as 2, 3, 4; Job#3 as
3, 4, 5.

For a maximum of EIGHT 1-digit numbers (no zeros!) in C1:J1:
=SUBSTITUTE(TRIM(SUBSTITUTE(SUMPRODUCT($C$1:$J$1*(C2:J2="X")*100^(COLUMN($J$1)-COLUMN($C$1:$J$1)));"0";"
"));" ";",")

C1:J1: 1 to 8
C2:J2: "X" or empty (consider use of Data Validity)

For your task replace all J's with G.

Why this maximum?
- XL can do up to 15-digit numbers only of which 7 are needed for comma
- there are only 9 different non-zero digits

 

[Alexander Wolff]

=SUBSTITUTE(TRIM(SUBSTITUTE(SUMPRODUCT($C$1:$J$1*(C2:J2="X")*100^(COLUMN($J$1)-COLUMN($C$1:$J$1)));"0";"

"));" ";",")

Sorry, for English Excel please change separators to ",":
=SUBSTITUTE(TRIM(SUBSTITUTE(SUMPRODUCT($C$1:$J$1*(C2:J2="X")*100^
(COLUMN($J$1)-COLUMN($C$1:$J$1))),"0"," "))," ",",")

 

[Bernie Deitrick]

In AH2, enter the formula

=IF(B2="X",B$1 & IF(COUNTIF(C2:$AG2,"X")>0,",",""),"")

and copy to the right for 30 more columns, to BL2.

Then use a formula like this in AG2:
=AH2 & AI2 & ..... & BL2

Then copy all those formulas down AG2 through BL2, and hide columns AH:BL.

A couple hundred rows of those won't affect calculation times.

HTH,
Bernie
MS Excel MVP



Thank you, Bernard. Yah, the commas are going to be pretty important,
so I'd better go with the second set up. Can I "&" 31 IF
statements?? Wonder what the recalc time is down 100-ish rows??

A UDF would be much cleaner, but I have people who don't know what to
do with a macro warning (they think a virus is trying to kill them!)
and others who simply don't know how or don't bother to turn macros
on.

I appreciate the boost.
Ed