How do you put the all the possible locations in one cell? this is hard

**pretenderoui** · 10-08-2010, 08:29 PM

Hi All ive tried to find a solution for this but i dont think anywone can help on this one.

Well, i have 3 colums A is (SKU) Colum B is ( ISBN) and colum C is ( Quantity ) i have 30,000 cells and my SKU is also used as my location for exaple my SKU is ( SHELF-1-01 , SHELF-2-02........... and so on) so because all my ISBN are dublicates i am using the formula to count B Colum in Colum C ( Quantity) and then i use the Remove dublicates. So far so good.

This is what i have now
A(SKU) B (ISBN) C(Quant)
SHELF-4-98 9781905151789 1
SHELF-1-07 9781905151789 1
SHELF-1-04 9781905151789 1
SHELF-7-01 9781905151789 1

Now i want each unique value that is left in colum B to tell me in how many locations it is so for example i want it to tell me

A(SKU) B (ISBN) C(Quant)
SHELF-4-98,SHELF-1-07,SHELF-1-04, 9781905151789 4

Thats the result i want to see , i hope my examples where clear, ive been looking for a solution for this for the past 2 years but cant find anything there is a way to remove duplicates and count the cell but for the location to scan and find and put them in one cell is hard.

Thanks for your time
Paul

**peterjuhnke** · 10-09-2010, 12:45 AM

Please attach a dummy sheet explaining what you want to see as a result, were you want the formula. etc.
Regards
Peter

**pretenderoui** · 10-09-2010, 04:55 AM

Hi thanks for the reply

i have attached a dummie file to show what i need, Sheet 1 shows what i have and Sheet 2 what i would need.

**peterjuhnke** · 10-09-2010, 08:30 PM

Hello
Please have a look at this, firs found all locations of each COL E:AS, then joined them together in Col A.
Cell stays blank if nothing found.
ROW 1 is the #th time it is in the list.
Hope this is what you are aftre.
Regards
Peter