Formula needed to copy certain data from Rows to Columns

[txart]

I am sure this is a simple fix for the experienced Excel user... and I apologize if this is explained in other threads. I could not find what I needed after two hours of searching. Anyhow... here it is... I have a file that is over 400,000 fields by two. A small portion of the file looks like:

A B
1 214570 porch
2 214570 realism
3 214570 red
4 214570 rocking
5 214570 rocking chairs
6 214570 traditional
7 51528 aa ap115
8 51528 abstract
9 51537 aa ap170
10 51537 alfred
11 51537 arrangement
12 51537 blue
13 51537 contemporary
14 51537 cornflower
.... and so on for 400,000 more fields

What I need to do is collect the data so the sku number is field A is not repeated and the info in B is collected all in one cell. So, it would look something like:

A B
1 214570 porch, realism, red, rocking, etc.
2 51528 aa ap115, abstract
3 51537 aa ap170, alfred, arrangement, etc


This seems easy to do... but I can't figure out a way. Your help would be greatly appreciated. Thanks.

 

[txart]

GTO...... When I put the cursor over the UBOUND... instead of

UBound(aryData, 1)=45000

I got....

UBound(aryData, 1)=2

Is there a way for me to change that to 45000? I hit f5 and nothing happened.

 

[GTO]

It is not finding data in COL A. WHat COL are the SKUs in?

 

[txart]

The SKU is in COL A and the keywords are in COL B

 

[GTO]

With everything exactly as was when you tested, run this:

Sub ACK()
Dim r As Range
    
    Set r = Cells(Rows.Count, 1).End(xlUp)
    MsgBox r.Address(0, 0) & " is located on " & r.Parent.Name & " in workbook: " & r.Parent.Parent.Name
End Sub

What's it say?

 

[txart]

It says....

A1 is located on Sheet1 in workbook: Book14

 

[GTO]

Sorry, I should have included that it should say the address of the last cell in SKUs, along with the name of the sheet that your data is on, and the name of the workbook that the data resides in. I feel safe in assuming that this is not the case?

Do you have the ability to upload the wb to Boxnet or some such share site. If you do, please ensure the file is saved in 97-2003 format (extension of .xls).

EDIT:

In VBIDE look at the project window. Does the module you placed the code in belong to the workbook with the data?

 

[Rick Rothstein]

Do you have the ability to upload the wb to Boxnet or some such share site. If you do, please ensure the file is saved in 97-2003 format (extension of .xls).

I would also be interested in seeing a copy of your workbook as the last code I posted works fine here on the sample data I made from your posting.

EDIT:

In VBIDE look at the project window. Does the module you placed the code in belong to the workbook with the data?

That was a good question to ask! The answer may well be at the root of the OP's problem.