How to get all the values in column A based on the value in column B

[mchlkwn]

Hello:

I have a task to accomplish but not sure if it is possible in Excel

In warehouse I work at stores many different products. Each product maybe located in different locations. All the products and their associated locations have been stored in an Excel file.

I need to list the associated locations of each product.

Attached Excel file is what the table looks like.


Thanks in advance.

[ajryan88]

Hi,

I have posted a solution in the green table beneath your sample solution table.

I hope this helps

[mchlkwn]

Many thanks except I don't follow the logic.

When I try to decompose part of the solution in cell F13

IF($B$2:$B$10=$E13,ROW($A$2:$A$10)-ROW($A$1))

Isn't there is a missing Else part but there is no error in the updated Excel file.

Please advise.

Hi,

I have posted a solution in the green table beneath your sample solution table.

I hope this helps

[ajryan88]

The Else argument is optional, and just returns a FALSE if omitted.

[mchlkwn]

What are those { and }? If I click the cell, I can see the { and }. However, if I click the formula bar, I don't see those anymore. Also, if I copy the formula without { and }, it does not work in other cell. If I copy the cell and paste it somewhere else in the worksheet, it still work.

The Else argument is optional, and just returns a FALSE if omitted.

[ajryan88]

Ah yes, sorry I should have explained that a bit better.

The curly braces indicate that the formula is an array formula. You do not type the braces in, they are entered automatically by Excel. To tell Excel that you want to enter an array formula, you use Ctrl + Shift + Enter instead of just Enter.

I hope this explains things a little bit better

[mchlkwn]

Many thanks:

That was the reason when I copied/pasted your formula, it was not working.

thanks again.

Ah yes, sorry I should have explained that a bit better.

The curly braces indicate that the formula is an array formula. You do not type the braces in, they are entered automatically by Excel. To tell Excel that you want to enter an array formula, you use Ctrl + Shift + Enter instead of just Enter.

I hope this explains things a little bit better

[ajryan88]

No problems

Please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks