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

[spaino]

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!

 

[mrhartley]

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]

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]

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]

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...

 

[shodan]

Made a mistake , here is the correct table; try this

test.xls
	A	B	C	D	E	F	G	H	I
1		1	1	2	2	3	3	4	4
2	1001	A	B	C	D	E	F
3	1001	C	D
4	1001	E	F
5	1002	G	H	I	J
6	1002	I	J
Sheet1

hope it helps

 

[spaino]

It does, thanks...! I think I'm on the right track now. Very useful.

 

[shodan]

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.