Many rows of data - I need data in 1 row only!!

spaino

New Member
Joined
Aug 19, 2005
Messages
4
Hi all.

I have an imported text file with an ID number in column A. There are many rows with the same value in colum A. However, all the rows have different values in columns B to E.

Here's an example:

Col A - Col B - Col C - Col D - Col E
1001 - 8196 - Type A - 06/12 - 26/05
1001 - 4296 - Type B - 21/08 - 26/10
1001 - 4298 - Type C - 21/08 - 26/10
1002 - 3050 - Type B - 01/04 - 07/04
1002 - 3052 - Type D - 01/04 - 07/04

I need all the data for "1001" in the same row. So as opposed to having three rows for "1001", I only have one with all the data from those rows moved to the one row. Then the same for "1002" and so on.

Is this possible? Maybe with a bit of VB???

Please HELP!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mrhartley

Well-known Member
Joined
Mar 8, 2005
Messages
564
Hi Welcome,

OK I start to see where your coming from but I don't see what you are trying to achieve.

Combining col A where Col is a match is going to help you do what?

The remaining columns do not match so you can't subtotal the rows where col A matches.

Can you post an example like you have already but with that you are hoping the result will look like.

Otherwise I recomend looking up 'subtotals' and pivot tables. Both of which I am sure you will find help here for too :)

Regards
Mark
 

spaino

New Member
Joined
Aug 19, 2005
Messages
4
Hi Mark

I've got a text file that has been exported from Business Objects. I need to get all the info on one line to import into another database. I thought maybe Excel would be the best tool to manipulate it this way. The reason it needs to be on one line is that the database can only accept one line per ID number. So if i had 3 or 4 rows with the same ID in col. A, it wouldn't work. I hope i'm making sense..

Here's an example of what i'm trying to get to:

Col A - Col B - Col C - Col D - Col E - Col F - Col G - Col H etc
1001 - 8196 - Type A - 06/12 - 26/05 - 4296 - Type B - etc
1002 - 4296 - Type B - 01/04 - 07/04 - 3052 - Type D - etc

Can't really fit it in here but if there were say, 3 rows originally for the ID 1001, I would want the info for columns B,C,D and E from rows 2 and 3, moved to the end of row 1.
 

mrhartley

Well-known Member
Joined
Mar 8, 2005
Messages
564
ah OK I see what your saying.

I don't think subtotals and pivot tables are going to help here and I think your right the VBA might be required.

Sadly I am no VBA guru, so I have to leave that for others.

Best of luck
Mark
 

spaino

New Member
Joined
Aug 19, 2005
Messages
4

ADVERTISEMENT

Cheers Mark

I think at its simplest it should (could) be an IF the IDs are equal in Col. A - move the details from that row to the end of the first row with the same ID. Or something...
 

spaino

New Member
Joined
Aug 19, 2005
Messages
4
It does, thanks...! I think I'm on the right track now. Very useful.
 

shodan

Active Member
Joined
Jul 6, 2005
Messages
481
ok, perfect, than really this thanks should go out to all the experts on the board (don't want to name them all because i don't want to forget one) who has been teaching me things like this in the past few months. Before this, I wasn't able at all to give you such a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,070
Members
412,762
Latest member
sienweiw
Top