Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 15th 08, 09:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 9
Default Transpose random series of cells

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
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.


  #2   Report Post  
Old November 16th 08, 06:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default Transpose random series of cells

Try the macro

Sub copy()
'This will read Sheet1 and write to Sheet2
'It is assumed that Sheet2 won't have anything from row 2 down
'It will overwrite if there is anything

Dim lastRow1 As Long
Dim i, j, k As Long
Dim id As String

'Find last row of data on Sheet1
With Worksheets("Sheet1")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

id = ""
'Change 1 to 0 below if you want to start at row 1 in Sheet2
j = 1

'Change 1 to 2 below if you have header rows
For i = 1 To lastRow1
If Worksheets("Sheet1").Cells(i, 1) = id Then
'As long as Id does not change write to the same row
Worksheets("Sheet2").Cells(j, k) = Worksheets("Sheet1").Cells(i, 2)
k = k + 1
Else
'start a new row when id changes
k = 3
j = j + 1
id = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(j, 1) = id
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2)

End If
Next i
MsgBox "Processing Complete"
End Sub


" 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
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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transpose random series of cells [email protected] Excel Worksheet Functions 0 November 15th 08 09:00 PM
how do I transpose a row of cells to a column between worksheets? Mike C[_3_] New Users to Excel 2 September 13th 07 05:41 PM
=TRANSPOSE: getting 0es but waste cells wanted LevT Excel Worksheet Functions 2 March 13th 07 02:44 PM
Transpose from one sheet to the next and leave out blank cells notso Excel Discussion (Misc queries) 2 January 31st 07 02:42 AM
Can I create a random order within a series of numbers in Excel? Dimtrax Excel Worksheet Functions 2 November 9th 05 04:40 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017