Comma separated values

**shrikantk** · 05-18-2009, 08:41 AM

Hi,
I have list of users belongs to each city, I want to autopopulated my summary sheet using formulas
Summary sheet includes City name and Comma separted User list.

I have attached sample excel workbook in that Sheet1 is summary page and Sheet2 is actual data entered/extracted from database.
Can anyone please look into and tell me which formula I can use? or If you have sample formula that solves my problem please share with me.

Thanks,

**DonkeyOte** · 05-18-2009, 09:28 AM

Perhaps along the lines of:

Please Login or Register  to view this content.

**shrikantk** · 05-18-2009, 09:44 AM

Hi
Thanks for response, Actually we are looking for formula instead of VBA code.
due to some security issue our subclients dont hv permission to set Macor settings.

Can you please help me on formula to get similar output?

Thanks,

**DonkeyOte** · 05-18-2009, 09:52 AM

I suspect with a bigger data set sample there will be other issues but you could do the following:

Sheet1!
C2: =1+(LEN(B1)-LEN(SUBSTITUTE(B1,",","")))
copy down

Sheet1!
D2: =SUM(C1:D1)
copy down (note use of row 1 is deliberate)

Once the above are in place

Sheet2!
A1: =LOOKUP(ROWS(A$1:A1)-1,Sheet1!$D:$D,Sheet1!A:A)
copy down

B1: =TRIM(MID(SUBSTITUTE(LOOKUP(ROWS(B$1:B1)-1,Sheet1!$D:$D,Sheet1!B:B),",",REPT(" ",100)),1+(100*(COUNTIF($A$1:$A1,$A1)-1)),100))
copy down

If you try to do this without appending Sheet1 you will end up with a very expensive approach in terms of overall performance.