Convert multi-line records to single line?

**automator** · 11-19-2009, 05:51 PM

I have about 2.5 million rows in the format listed below. I was wondering if there was an easy way (in excel or access) to format them to the second example listed below.
So that if <column A> has multiple matching values all of their <column B> values could be moved to a single line.

100 BOB
100 JON
100 MARK
101 BOB
101 TIM
102 CHRIS

I would like to convert it to :
100 BOB JON MARK
101 BOB TIM
102 CHRIS

**shg** · 11-19-2009, 05:54 PM

These aren't in Excel; are they in text files? If so, post an abbreviated example.

**zbor** · 11-19-2009, 05:59 PM

Here, try this:

Samplebook(1).xls

**automator** · 11-19-2009, 06:07 PM

I attached a img of the some screenshots to help show what i mean

*All names used have been changed to fake names

**martindwilson** · 11-19-2009, 06:08 PM

i wonder how long that would take to calculate on 2.5 million rows?(not that there are in xl2003,or 2007 either!)
its not a criticism but a genuine interest.

**automator** · 11-19-2009, 06:11 PM

Originally Posted by martindwilson

i wonder how long that would take to calculate on 2.5 million rows?
its not a criticism but a genuine interest.

Well since its being one in ether excel 2003 it will be done 65k records at a time =x

unless access has a better / faster way.

**martindwilson** · 11-19-2009, 06:18 PM

thats why shg asked how they are currently stored eg text probably with a view to using some code to import it

**shg** · 11-19-2009, 06:38 PM

If they were on a sheet as described,

Please Login or Register  to view this content.

If they were in a text file, I'd do it from the text file.

**automator** · 11-20-2009, 11:48 AM

You sir are brilliant!! Thank you very much!

A note I wanted to add to the vba snippet posted above.

*It works great except the very last record it doesn't move. I move it manualy however I'm sure if you are better at excel vba you can fix this yourself.

I included a small img to show what I mean. I only used "100" for matching criteria. If you have a large range of numbers it only doesnt move the very last set of matching numbers. I hope this makes sense =x

**SWAPNILAGRAWAL** · 06-07-2010, 09:37 AM

Could you please help me to Read this from TEXT file .
In My case TEXt file is something like this

1235 1/1/2002 ghhjkjk
fdgsgfh
fdfgdfdhjh
2/1/2002 ggdfdhjjk
fhdgfd
1236 1/2/2002 ghdfgjd
1237 1/2/2002 hgjfhkgk

and i want to read it like this

1235 1/1/2002 ghhjkjkfdgsgfhfdfgdfdhjh
1235 2/1/2002 ggdfdhjjkfhdgfd
1236 1/2/2002 ghdfgjd
1237 1/2/2002 hgjfhkgk

I can open it in Excel too so anything would be fine. Thanks in advance

**shg** · 06-07-2010, 09:46 AM

Welcome to the forum, SWAPNILAGRAWAL.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

Convert multi-line records to single line?

LinkBack

Thread Tools

Display

Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Re: Convert multi-line records to single line?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions