I have a 60,000 long series of items that correspond with a range of
values. I need to automatically transpose the corresponding values
so
that they can be combined into one cell.

Here's what I have:

A B
1.2101R 1992
1.2101R 1993
1.2101R 1994
1.2101R 1995
1.2102G 1986
1.2102G 1987
10.1101G 1963
10.1101G 1964
10.1101G 1965
10.1101G 1966
10.1101G 1967
10.1101G 1968


Here's what I need

A B C D E
1.2101R 1992 1993 1994 1995
1.2102G 1986 1987
10.1101G 1963 1964 1965 1966 1967 1968


As you can see there are different quantities with each item.

sheet 1 - has data

In sheet 2 -
go to data | filter | advance filter | choose the action: copy to
another location | list range : select range A1 : A100 | sheet 2 cell
A1 | check unique records only | ok

Note : In your range if the header is not specified then your filtered
value will as
1.2101R
1.2101R
1.2102G
10.1101G

Delete the first value and now in cell B1 ( sheet 2 ) put this
formula ( use Ctrl + Shift + Enter )

=IF(ISERROR(INDEX(sheet1!$B$1:$B$20,SMALL(IF(sheet 2!$A1=sheet1!$A$1:$A
$20,ROW(sheet1!$A$1:$A$20)),COLUMN(A:A)),0)),"",IN DEX(sheet1!$B$1:$B
$20,SMALL(IF(sheet2!$A1=sheet1!$A$1:$A$20,ROW(shee t1!$A$1:$A
$20)),COLUMN(A:A)),0))

Once u enter the formula use ctrl + shift + enter

Now, select B1 : G20 hit ctrl + r & then ctrl + d

You will see the desired result.





On Nov 16, 1:06*am, wrote:
I have a 60,000 long series of items that correspond with a range of
values. *I need to automatically transpose the corresponding values
so
that they can be combined into one cell.

Here's what I have:

A * * * * * *B
1.2101R 1992
1.2101R 1993
1.2101R 1994
1.2101R 1995
1.2102G 1986
1.2102G 1987
10.1101G * * * *1963
10.1101G * * * *1964
10.1101G * * * *1965
10.1101G * * * *1966
10.1101G * * * *1967
10.1101G * * * *1968

Here's what I need

A * * * * * B * * * C * * D * * *E
1.2101R 1992 1993 1994 1995
1.2102G 1986 1987
10.1101G 1963 1964 1965 1966 1967 1968

As you can see there are different quantities with each item.

On Sat, 15 Nov 2008 12:06:41 -0800 (PST), wrote:

I have a 60,000 long series of items that correspond with a range of
values. I need to automatically transpose the corresponding values
so
that they can be combined into one cell.

Here's what I have:

A B
1.2101R 1992
1.2101R 1993
1.2101R 1994
1.2101R 1995
1.2102G 1986
1.2102G 1987
10.1101G 1963
10.1101G 1964
10.1101G 1965
10.1101G 1966
10.1101G 1967
10.1101G 1968


Here's what I need

A B C D E
1.2101R 1992 1993 1994 1995
1.2102G 1986 1987
10.1101G 1963 1964 1965 1966 1967 1968


As you can see there are different quantities with each item.

In order to combine them into one cell, you are going to probably need a VBA
Macro. With 60,000 items, I suspect my contribution will take a while to run,
but it might be sufficient for you.

To enter this macro, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Since I don't know the setup of your worksheet, I assumed that your items and
values were listed in columns A & B, and that you have selected some cell
within the table.

I also chose to begin the results showing in F2, and to continue down below
that. You should be able to change these appropriately.

In any event, it should give you a start.

After you have entered the Macro, and also selected a cell within your table,
<alt-F8 opens the macro dialog box. Select the Macro and <RUN.

================================================
Option Compare Text
Option Explicit
Sub CombineData()
Dim rSrc As Range, rSrcFirstCol As Range
Dim rDest As Range
Dim c As Range
Dim UniqueItems As Variant
Dim i As Long, j As Long
Dim lStartRow As Long

'Many ways to set up the range of data
Set rSrc = Selection.CurrentRegion
Set rSrcFirstCol = rSrc.Resize(columnsize:=1)
Set rDest = Range("F2")
'get number of unique items and
'number of values per item
UniqueItems = UniqueCount(rSrcFirstCol)
rDest.Resize(UBound(UniqueItems, 2), 1).ClearContents

For i = 1 To UBound(UniqueItems, 2)
rDest(i, 1) = UniqueItems(0, i)
For j = 1 To UniqueItems(1, i)
For Each c In rSrcFirstCol
If c.Value = UniqueItems(0, i) Then
rDest(i, 1) = rDest(i, 1) & " " & c.Offset(0, 1).Value
j = j + 1
End If
Next c
Next j
Next i
End Sub

Function UniqueCount(rg As Range)
'Returns a horizontal two dimensional
' array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range

'get list of unique words
Set cWordList = New Collection

On Error Resume Next
For Each c In rg
cWordList.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0

ReDim sRes(0 To 1, 1 To cWordList.Count)
For i = 1 To cWordList.Count
sRes(0, i) = cWordList(i)
Next i

'get word count for each word
For i = 1 To UBound(sRes, 2)
sRes(1, i) = Application.WorksheetFunction.CountIf(rg, sRes(0, i))
Next i

'sort by Count highest to lowest
BubbleSortX sRes, 1, False

'Sort words alphabetically A-Z
BubbleSortX sRes, 0, True

UniqueCount = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSortX(TempArray As Variant, d As Long, _
bSortDirection As Boolean)
'bSortDirection = True means sort ascending
'bSortDirection = False means sort descending
Dim Temp1 As Variant, Temp2
Dim i As Long
Dim NoExchanges As Boolean
Dim Exchange As Boolean

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 1 To UBound(TempArray, 2) - 1

' If the element is greater/less than the element
' following it, exchange the two elements.

Exchange = TempArray(d, i) < TempArray(d, i + 1)
If bSortDirection = True Then Exchange = _
TempArray(d, i) TempArray(d, i + 1)
If Exchange Then
NoExchanges = False
Temp1 = TempArray(0, i)
Temp2 = TempArray(1, i)
TempArray(0, i) = TempArray(0, i + 1)
TempArray(1, i) = TempArray(1, i + 1)
TempArray(0, i + 1) = Temp1
TempArray(1, i + 1) = Temp2
End If
Next i
Loop While Not (NoExchanges)
End Sub
=====================================
--ron

Is your Column A data grouped by like items as shown (1.2101R grouped
together, 10.1101G grouped together, etc.)? In other words, is it possible
for 1.2101R to appear where shown and then again much further down the list?
Also, where did you want your results?

--
Rick (MVP - Excel)


wrote in message
...
I have a 60,000 long series of items that correspond with a range of
values. I need to automatically transpose the corresponding values
so
that they can be combined into one cell.

Here's what I have:

A B
1.2101R 1992
1.2101R 1993
1.2101R 1994
1.2101R 1995
1.2102G 1986
1.2102G 1987
10.1101G 1963
10.1101G 1964
10.1101G 1965
10.1101G 1966
10.1101G 1967
10.1101G 1968


Here's what I need

A B C D E
1.2101R 1992 1993 1994 1995
1.2102G 1986 1987
10.1101G 1963 1964 1965 1966 1967 1968


As you can see there are different quantities with each item.

Yes, each grouping is together 1.2101R, 10.1101G, etc. in column A,
and the year is in column B in sequential order. Ultimately, what I
need is for the results to be in one cell with some formatting. For
example, results for 1.2101R in C1 (shown in quotes): "1992 , 1993 ,
1994 , 1995" with the spacing and commas.

Okay, it looks like you now want the quoted string of comma separated years
in Column C next to (I'm guessing) the first occurrence of each of the
grouped data (so the output will be place in C1, C5 and C7 for your example
code). If that is correct, then the following macro should do what you
want...

Sub GroupData()
Dim X As Long
Dim LastRow As Long
Dim Data As String
Dim Dates As String
Dim Cel As Range
'
Const SheetName As String = "Sheet9"
Const DataStartRow As Long = 1
'
With Worksheets(SheetName)
Set Cel = .Cells(DataStartRow, "A")
Data = Cel.Value
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = DataStartRow To LastRow + 1
If Data < .Cells(X, "A").Value Then
Cel.Offset(, 2).Value = """" & Left(Dates, Len(Dates) - 3) & """"
Set Cel = .Cells(X, "A")
Data = Cel.Value
Dates = ""
End If
Dates = Dates & .Cells(X, "B").Value & " , "
Next
End With
End Sub

To implement this code, press Alt+F11 to get into the Visual Basic editor,
click Insert/Module on its menu bar and copy/paste the above code into the
code window that opened. Once you have done that, edit the two separated
Const statements changing my example worksheet name from Sheet9 to the
actual name of the worksheet with your data on it and changing the data
start row from 1 to whatever row your data actually starts on. Now, go back
to the worksheet and press Alt+F8, select GroupData from the list and click
the Run button. You say you have 60,000 rows of data, so I'm guessing the
code will take some time to run (I do not have a feel for how long).

--
Rick (MVP - Excel)


wrote in message
...
Yes, each grouping is together 1.2101R, 10.1101G, etc. in column A,
and the year is in column B in sequential order. Ultimately, what I
need is for the results to be in one cell with some formatting. For
example, results for 1.2101R in C1 (shown in quotes): "1992 , 1993 ,
1994 , 1995" with the spacing and commas.

Rick: That worked fabulously! Ironically, it also highlighted some
quirks in my dataset. Most of the time my data only has one series of
years, but every so often there's more than one series of years.
Here's an example with the results of your VB code in column C

Col A Col B Col C
11.7102R 1979 "1979 , 1980 , 1981 , 1982 , 1983 , 1984 , 1985"
11.7102R 1980
11.7102R 1981
11.7102R 1982
11.7102R 1983
11.7102R 1984
11.7102R 1985
12.3101G 1966 "1966 , 1967 , 1968 , 1969 , 1970 , 1971 , 1972 , 1973 ,
1974 , 1975 , 1968 , 1969 , 1970 , 1971 , 1971 , 1972 , 1973 , 1974"
12.3101G 1967
12.3101G 1968
12.3101G 1969
12.3101G 1970
12.3101G 1971
12.3101G 1972
12.3101G 1973
12.3101G 1974
12.3101G 1975
12.3101G 1968
12.3101G 1969
12.3101G 1970
12.3101G 1971
12.3101G 1971
12.3101G 1972
12.3101G 1973
12.3101G 1974

You can see that 11.7102R only has one series of years, but 12.3101g
has three different series of years. I need the years pulled out each
time the series of years starts over. I.e. for 12.3101g 1966 though
75 and 12.3101g for 1968-71 and 12.3101g for 71-74

Also, I just attempted to run it on the entire dataset, and am getting
a "out of memory" error. Any suggestions?