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.

 

[GTO]

What is your OS and Excel ver? e.g. Windows XP / Excel 2000

Does your data look like I have in COLs A and B in Post#7 ?

 

[txart]

I have Windows 7... Excel 2007... I did not get the same result... but that is exactly what I am trying to do. I'm sure I made a mistake somewhere... any ideas?

 

[txart]

I think I misunderstood your question..... Yes, before entering any formulas or macros... you have col A and B correct. The results you get in the other columns is what I am looking to do.... only.... col A and B go on for more than 400,000 rows.

 

[Rick Rothstein]

I think I misunderstood your question..... Yes, before entering any formulas or macros... you have col A and B correct. The results you get in the other columns is what I am looking to do.... only.... col A and B go on for more than 400,000 rows.

As written, mine is probably not working because of the size of your data. In versions of Excel before XL2010, the SpecialCells function I am using can only handle a maximum of 8196 individual areas of data... with 400,000 rows of data, I'm betting you will have more than 8196 rows for answers... hence, my code won't work. Let me reconfigure it to handle data in chunks rather than attempt to handle all the data at once. I'll be back in a little bit.

 

[GTO]

Did you test the code I provided at #7? If so, did you change the sheetname in the code to reflect your sheet's name? What happened when you ran the code?

Of course test in a junkl copy of your wb.

 

[txart]

I will try that again..... I put everything in sheet4 and ran it... but I will try changing the code this time

 

[txart]

I keep getting something that says... exa1.... I can't hit Run, but I hit create and then another window pops up and I can then run module1.exa1. But, it doesn't work.

 

[GTO]

Please follow my instructions explicitly.
In VBIDE and looking at the window with the code you pasted into it (Sub exa1()), look at the main titlebar at the top. It should say

• workbookname.xls - [Module1(Code)]

...where the 1 may be a different number. If it is something else, like
....-[Sheet1(Code)] , then we have the code in a sheet's module.
If is IS in a Standard Module, then click inside the procedure someplace and start pressing the F8 key until the yellow highlight has passed:

ReDim aryOutput(1 To UBound(aryData, 1), 1 To 2)

Now place the cursor over UBound and a little box should pop-up:
UBound(aryData, 1)=45000

Did that work? If yes, press F5 and see what results we get.

 

[Rick Rothstein]

I'll be back in a little bit.

Okay, I'm back. Give this macro a try...

Sub TransposeData()
  Dim X As Long, LastRow As Long, Blank As Long, Index As Long, Data As Variant, DataString As String
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(StartRow - (StartRow = 1), "C").Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=if(rc1=r[-1]c1,""X"","""")"
  End With
  Data = Cells(StartRow, "A").Resize(LastRow - StartRow + 1, 3)
  Blank = LBound(Data)
  For X = LBound(Data) + 1 To UBound(Data)
    If Data(X, 3) = "X" Then
      DataString = DataString & ", " & Data(X, 2)
    Else
      Data(Blank, 2) = Data(Blank, 2) & DataString
      DataString = ""
      Blank = X
    End If
  Next
  Index = 1
  For X = LBound(Data) To UBound(Data)
    If Data(X, 3) <> "X" Then
      Cells(Index, "D").Value = Data(X, 1)
      Cells(Index, "E").Value = Data(X, 2)
      Index = Index + 1
    End If
  Next
  Columns("C").Clear
  Application.ScreenUpdating = True
End Sub

 

[txart]

Rick... I tried it and all it returned was:

<table border="0" cellpadding="0" cellspacing="0" width="128"><col width="64" span="2"><tr height="19"> <td style="height: 14.5pt; width: 48pt;" width="64" height="19">PID</td> <td style="width: 48pt;" width="64">SEARCHKEYWORD</td> </tr></table>
Basically... just the Headers... no info. Do I need to have anything highlighted when I hit alt+F8?