look up and append result to row for multiple identical entries
 

Hi!
I hope my subject line was somewhat descriptive.

I have the following problem:
1. Column A of my extremely huge spreadsheet contains id numbers.
2. ID numbers appear more than once, because the same case is sprea
across multiple rows (i didn't set this thing up...)
3. I need each unique case to be in one single row.

Basically, I need to convert ...

A B C D
id1 1 2 1
id1 3 4 5
id2 2 1 5
id2 9 2 3

...to

A B C D E F G
id1 1 2 1 3 4 5
id2 2 1 5 9 2 3

Added difficulty:
The number of rows over which one unique case spreads is not constant
E.g., I have 10 id1, 20 id2, 15 id3...


Can anyone help? I'd be forever grateful!

a

--
as_sas
-----------------------------------------------------------------------
as_sass's Profile: http://www.excelforum.com/member.php...nfo&userid=906
View this thread: http://www.excelforum.com/showthread.php?threadid=39995

look up and append result to row for multiple identical entries
 

How about this?

Sub Macro1()
Range("A1").CurrentRegion.Select
Selection.Sort Key1:=Range("A1")
lrow = Selection.Rows.Count

For i = lrow To 2 Step -1
If Range("A" & i) < Range("A" & i - 1) Then GoTo donothing
lcol = Range("A" & i, Range("A" & i).End(xlToRight)).Columns.Count
Range(Cells(i, 2), Cells(i, lcol)).Copy
lcol = Range("A" & i - 1, Range("A" & i - 1).End(xlToRight)).Columns.Count
Cells(i - 1, lcol + 1).Select
ActiveSheet.Paste
Range("A" & i).EntireRow.Delete
donothing:
Next i

End Sub


"as_sass" wrote:


Hi!
I hope my subject line was somewhat descriptive.

I have the following problem:
1. Column A of my extremely huge spreadsheet contains id numbers.
2. ID numbers appear more than once, because the same case is spread
across multiple rows (i didn't set this thing up...)
3. I need each unique case to be in one single row.

Basically, I need to convert ...

A B C D
id1 1 2 1
id1 3 4 5
id2 2 1 5
id2 9 2 3

...to

A B C D E F G
id1 1 2 1 3 4 5
id2 2 1 5 9 2 3

Added difficulty:
The number of rows over which one unique case spreads is not constant.
E.g., I have 10 id1, 20 id2, 15 id3...


Can anyone help? I'd be forever grateful!

as


--
as_sass
------------------------------------------------------------------------
as_sass's Profile: http://www.excelforum.com/member.php...fo&userid=9065
View this thread: http://www.excelforum.com/showthread...hreadid=399950

look up and append result to row for multiple identical entries
 

Works like a charm!
Can I send you flowers?!?

as

PS: actually, just ran into a problem:
I have several ID numbers that appear more than 250 times.
That means I'll run into the problem that the worksheet is not "long
enough.

Apart from "use a database and not Excel", does anybody know a solutio
to this problem? Like writing the whole thing as one row to a text file
for example?

Thanks!

a

--
as_sas
-----------------------------------------------------------------------
as_sass's Profile: http://www.excelforum.com/member.php...nfo&userid=906
View this thread: http://www.excelforum.com/showthread.php?threadid=39995