Lookup function, but then a bit complicater

[Robert]

I am having the following set of data. > 14000 rows.

A B
1010071 80425
1010071 80253
1010071 80425
1010071 80253
1010083 80451
1010083 80205
1010083 81853
1010083 80451
1010083 80205
1010083 81853
and so on...

What I want to is this:

Make a colum for C: Looking like this:

1010071
1010083
and so on.. = peace of cake and is done.

But now is the problem.

Behind this colum I want some colums holding the data for B.

Looking like this

1010071 80425 80253
1010083 80451 80205 81853 80451
80205 81853
and so on....

I am having absolutely NO IDEA how to fix this one. any help would be
welcome since doing this by hand is too much..I am pretty familiar
with excel ( and a little with VBA)

Thanks for any advice.

Robert

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1
If .Cells(i, TEST_COLUMN).Value = _
.Cells(i - 1, TEST_COLUMN).Value Then

.Cells(i, "B").Resize(, 250).Copy .Cells(i - 1, "C")
.Rows(i).Delete
End If
Next i

End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[paul.robinson]

I am having the following set of data. > 14000 rows.

A B
1010071 80425
1010071 80253
1010071 80425
1010071 80253
1010083 80451
1010083 80205
1010083 81853
1010083 80451
1010083 80205
1010083 81853
and so on...

What I want to is this:

Make a colum for C: Looking like this:

1010071
1010083
and so on.. = peace of cake and is done.

But now is the problem.

Behind this colum I want some colums holding the data for B.

Looking like this

1010071 80425 80253
1010083 80451 80205 81853 80451
80205 81853
and so on....

I am having absolutely NO IDEA how to fix this one. any help would be
welcome since doing this by hand is too much..I am pretty familiar
with excel ( and a little with VBA)

Thanks for any advice.

Robert

Hi
1.Do text to columns, using the space to seperate them. Soppose the
data is now in columns A and B and starts in row 1
2. Type this formula in C1
=IF($A16=$A15,B16,"")
3. Drag the formula down column C
4. If there are any none blanks in column C, drag the formula to
column D. Repeat until you have a column of blanks
5. Copy all your data and paste special...values to where it is, so
that you remove the formulas.

regards
Paul

 

[paul.robinson]

Hi
1.Do text to columns, using the space to seperate them. Soppose the
data is now in columns A and B and starts in row 1
2. Type this formula in C1
=IF($A16=$A15,B16,"")
3. Drag the formula down column C
4. If there are any none blanks in column C, drag the formula to
column D. Repeat until you have a column of blanks
5. Copy all your data and paste special...values to where it is, so
that you remove the formulas.

regards
Paul- Hide quoted text -

- Show quoted text -

Oops
Forgot to add the filtering bits:

0. Highlight the two columns, and do an advanced filter...choose "copy
to another location" and select unique items. Choose the Copy To cell
on a new sheet in A1
....
and
6. Highlight column A and do another advanced filter...choose "Filter
the list in place" and select unique items
7. Copy the filtered data to a new location and you are done.

regards
Paul

 

[Robert]

Thank you for the advice. I managed to create my routings. The filter
didnt do what it is supposed to do. Maybe beaceause of some
inconsistent data.

I second row, I replaced the codes with letters, so I could easily
concatenate them.

1010479 L LKRM
1010479 K KRM
1010479 R RM
1010479 M M
1010490 H HSN HSN HSN
1010490 S SN SN
1010490 N N N



What I now want is only the above in 2 new colums.
1010479 LKRM
1010490 HSN

Is there an easy way to do is also?

 

[paul.robinson]

Hi
Put column headings on your data.
Select the first column only
Do Filter, Advanced Filter, Filter in place and tick "Unique Items
Only".
Copy what you get to a new location and delete the columns you don't
want.

regards
Paul

 

[Robert]

Eureka! It worked fine. Thank you sooo much

Hi
Put column headings on your data.
Select the first column only
Do Filter, Advanced Filter, Filter in place and tick "Unique Items
Only".
Copy what you get to a new location and delete the columns you don't
want.

regards
Paul