Converting column ids to comma delimited list 2

[peterswan]

Hello,

Is there a way to take a ton of rows of an id in the first column, and output them into a comma delimited list so I can load them into a SQL query and search on them?

Once I saw someone do this with the concatenate function but I can't remember how it was done. If I could just add a comma on the end of every instance of the id, I'd be solved.

Thanks,

Peter

 

[xlbo]

If they start in A1 then in B2 enter:

=A1&","&A2

In B3 enter:

=B2&","&A3

and copy down

copy the last cell and paste as values et voila

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[peterswan]

Hi Geoff,

Thanks for your reply.

This sounds ok but I've got 3000+ rows. If I had to copy and paste a different formula into each row, that would take forever, unless I didn't understand your methodology correctly.

All I need is to add a comma onto the end of each id, and then copy the whole column, then paste into SQL Query Analyzer and run my query.

Thanks for your help,



Peter

 

[TonyJollans]

Hi peterswan,

All you need do is copy Geoff's formula from B3 all the way down to B3000+ and Excel will adjust the references so that B3000, say, will contain [blue]=B2999&","&A3000[/blue].

If you just want a list of each value separately with a comma on the end ..

In B1 enter =A1&"","
Copy it down to B3000+ (the end of your data)

.. although they will be formulae in the worksheet, when you copy the column to another app it will copy the values.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[peterswan]

yes,

that is amazing. I never thought Excel would understand how to adjust the references to different cells when I copy over a whole formula.

Thanks for sticking with this one.

There was no way I was going to type in 3000 commas + down arrow all day!

Cheers,



Peter

 

[bluedragon2]

Got a little time, so I will give a little lesson why it does it:

Lets take cell B2

=B2 is a referance to the cell B2. Now, we can add something to that little formula to make it partly absolute or completely absolute. But first, lets say we have that formuls (=B2) in cell C4. If I copy and paste it as is to cell E7, excell will change the B2 by the offset that we pasted and turn it to =D5. We copied it from C4 two columns over and 3 columns down, so, excel add two columns and three rows to the B2.

Now lets change the formula to =$B2. This makes the column reference (B) absolute (will not change). So if we do the same copy and paste as above, the result will be =$B5. Excel added three rows to the orginal, but left the absolute B intact.

Same if we have =B$2. This will make the row value absolute and pasting like above will result in =D$2, which left the absolute row value intact, but added two colums to the B.

A complete absolute reference would look like =$B$2. If you would copy and paste it anywhere, it will remain the same.

Hope this explains it



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...