need some quick help for a work project

[cirix20]

I am trying to even see if this is possible. I am familiar with excel but not an expert just general work use. I have an excel with a list of 18 names in column A and column b has yes or no and i want c1 for example to list the names in column A that have a no in column b. Either together in the same cell separated by commas for transposed across C D E F etc.

 

[Sgdva]

I'll save you the time to google Lookupconcat function

 

[CooperJExcel]

@Sgdva, that is a good solution but if you aren't good at Excel it may be kind of tricky.

@cirix20, is this something you do all the time so you need an automated solution or do you just need to do this once?

 

[cirix20]

I'll save you the time to google Lookupconcat function

So I installed the UDF in the Module.

I am still having some trouble, it looks like the example in the link he is looking for Asia Pacific to be shown


My column A has Names and D the Yes or No


Name Completed No's
bob Yes Sue, George
sue No
George No
jeff yes


=LookUpConcat("NO",Sheet2!A6:A16,Sheet2!D6:D16,",")

cell comes up blank, not sure where I am going wrong

 

[cirix20]

@Sgdva, that is a good solution but if you aren't good at Excel it may be kind of tricky.

@cirix20, is this something you do all the time so you need an automated solution or do you just need to do this once?

in reality we have 18 location that report data on excel

I rolled all of the data into one network wide excel for reporting so everything is already linked over.

once I place the formula in the cell for each area I want it to report the people that had no's it will auto update based on the data that gets entered

 

[cirix20]

So I installed the UDF in the Module.

I am still having some trouble, it looks like the example in the link he is looking for Asia Pacific to be shown


My column A has Names and D the Yes or No


Name Completed No's
bob Yes Sue, George
sue No
George No
jeff yes


=LookUpConcat("NO",Sheet2!A6:A16,Sheet2!D6:D16,",")

cell comes up blank, not sure where I am going wrong

I got it to work. I have the ranges backwards. thanks it worked great

 

[cirix20]

Is it possible to do a If statement and the lookupconcat together.

for example- i tried my hand at it and got a #value in the result cell

=IF(S24=B18,LookUpConcat(Sheet2!$D$6:$D$16,Sheet2!$A$6:$A$16,","),IF(S24=F18,LookUpConcat(Sheet2!$H$6:$H$16,Sheet2!$A$6:$A$16,","),IF(S24="J18",LookUpConcat(Sheet2!$L$6:$L$16,Sheet2!$A$6:$A$16,","),IF(S24="N18",LookUpConcat(Sheet2!$P$6:$P$16,Sheet2!$A$6:$A$16,",")))))

 

[Sgdva]

Is it possible to do a If statement and the lookupconcat together.

for example- i tried my hand at it and got a #value in the result cell

=IF(S24=B18,LookUpConcat(Sheet2!$D$6:$D$16,Sheet2!$A$6:$A$16,","),IF(S24=F18,LookUpConcat(Sheet2!$H$6:$H$16,Sheet2!$A$6:$A$16,","),IF(S24="J18",LookUpConcat(Sheet2!$L$6:$L$16,Sheet2!$A$6:$A$16,","),IF(S24="N18",LookUpConcat(Sheet2!$P$6:$P$16,Sheet2!$A$6:$A$16,",")))))

You are missing what it going to concate
Quoting your message when it worked
LookUpConcat("NO",Sheet2!A6:A16,Sheet2!D6:D16,",") vs IF(S24=B18,LookUpConcat(Sheet2!$D$6:...
Do you spot the "NO" missing at the beginning?

 

[cirix20]

found my error after posting, i didnt include the "No", part of the lookupconcat

=IF(S24=$B$18,LookUpConcat("No",Sheet2!$D$6:$D$16,Sheet2!$A$6:$A$16,","),IF(S24=$F$18,LookUpConcat("No",Sheet2!$H$6:$H$16,Sheet2!$A$6:$A$16,","),IF(S24=$J$18,LookUpConcat("No",Sheet2!$L$6:$L$16,Sheet2!$A$6:$A$16,","),IF(S24=$N$18,LookUpConcat("No",Sheet2!$P$6:$P$16,Sheet2!$A$6:$A$16,",")))))