Transpose rows of data to single row if they share something in common

U

usernamem14

New Member
Joined
Sep 20, 2011
Messages
3
Hi everyone! I was hoping someone can help me with this transposing issue.

In column A, there are rows that represent different products. Column B represents the different package sizes the product comes in, which varies for each product. If it's helpful, I can tell you that the number of package sizes is as high as 7. For the matching products, I would like to transpose the data from Column B (their package sizes) to the first row of each product in the same order it was found. The raw data has about 40,000 rows and is always increasing. I am using Microsoft Excel 2000.


Sample Raw Data Before:
<table border="0" cellpadding="0" cellspacing="0" height="462" width="217"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Column A</td> <td style="width:48pt" width="64">Column B</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>1g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>2g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>3g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>5g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>3g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>2g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>1g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>1g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>2g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>3g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>5g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>6g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>5g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>3g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>2g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>1g</td> </tr> </tbody></table>

Sample Would like After:
<table border="0" cellpadding="0" cellspacing="0" height="126" width="622"><colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Column A</td> <td style="width:48pt" width="64">Column B</td> <td style="width:48pt" width="64">Column C</td> <td style="width:48pt" width="64">Column D</td> <td style="width:48pt" width="64">Column E</td> <td style="width:48pt" width="64">Column F</td> <td style="width:48pt" width="64">Column G</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>1g</td> <td>2g</td> <td>3g</td> <td>4g</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>5g</td> <td>4g</td> <td>3g</td> <td>2g</td> <td>1g</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>1g</td> <td>2g</td> <td>3g</td> <td>4g</td> <td>5g</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>6g</td> <td>5g</td> <td>4g</td> <td>3g</td> <td>2g</td> <td>1g</td> </tr> </tbody></table>


I found the macro below, but it only transposes to Column A through E. For example, product D has 6 package sizes, but the macro only returns 4 sizes.

Macro:
HTML: 
Sub lineemup()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = Cells(i + 1, 1) Then
nc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i + 1, 2).Resize(, nc).Copy Cells(i, nc)
Rows(i + 1).Delete
End If
Next i
End Sub
Results of above macro:
<table border="0" cellpadding="0" cellspacing="0" height="126" width="463"><colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Column A</td> <td style="width:48pt" width="64">Column B</td> <td style="width:48pt" width="64">Column C</td> <td style="width:48pt" width="64">Column D</td> <td style="width:48pt" width="64">Column E</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A</td> <td>1g</td> <td>2g</td> <td>3g</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">B</td> <td>5g</td> <td>4g</td> <td>3g</td> <td>2g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">C</td> <td>1g</td> <td>2g</td> <td>3g</td> <td>4g</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">D</td> <td>6g</td> <td>5g</td> <td>4g</td> <td>3g</td> </tr> </tbody></table>


Please let me know if additional information is needed. Thanks for your help in advance!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Give this macro a try...

Code: 
Sub TransposeData()
  Dim UnusedColumn As Long, LastRow As Long, A As Range
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .Offset(1).FormulaR1C1 = "=IF(R[-1]C1<>RC1,""X"","""")"
    .Value = .Value
    .SpecialCells(xlConstants).EntireRow.Insert
  End With
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For Each A In Cells(StartRow, "B").Resize(LastRow - StartRow + 1).SpecialCells(xlConstants).Areas
    A(1).Resize(, A.Rows.Count) = WorksheetFunction.Transpose(A)
  Next
  Cells(StartRow, "C").Resize(LastRow - StartRow + 2).SpecialCells(xlBlanks).EntireRow.Delete
  Cells(Rows.Count, UnusedColumn).End(xlUp).Clear
End Sub
 
Upvote 0
usernamem14,


Welcome to the MrExcel forum.


Sample raw data (already sorted/grouped per your screenshot) on worksheet Sheet1:


Excel Workbook
AB
1A1g
2A2g
3A3g
4A4g
5B5g
6B4g
7B3g
8B2g
9B1g
10C1g
11C2g
12C3g
13C4g
14C5g
15D6g
16D5g
17D4g
18D3g
19D2g
20D1g
21
Sheet1





After the macro on a new worksheet Results:


Excel Workbook
ABCDEFGH
1A1g2g3g4g
2B5g4g3g2g1g
3C1g2g3g4g5g
4D6g5g4g3g2g1g
5
Results





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Option Base 1
Sub ReorgData()
' hiker95, 09/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=580161
Dim w1 As Worksheet, wR As Worksheet
Dim A(), B(), O(), k
Dim d1 As Object
Dim r As Long, c As Long
Dim s As Long, e As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
w1.Activate
r = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
A = Range("A1:A" & r).Value
B = Range("B1:B" & r).Value
Set d1 = CreateObject("scripting.dictionary")
For r = LBound(A) To UBound(A)
  If Not d1.exists(A(r, 1)) Then d1(A(r, 1)) = d1.Count
Next r
k = d1.Keys
ReDim O(1 To d1.Count, 1 To 8)
For r = 1 To d1.Count
  O(r, 1) = k(r - 1)
Next r
For r = LBound(O) To UBound(O)
  s = Application.Match(O(r, 1), A, 0)
  e = Application.Match(O(r, 1), A, 1)
  If s = e Then
    O(r, 2) = B(s, 1)
  Else
    c = 1
    For n = s To e Step 1
      c = c + 1
      O(r, c) = B(n, 1)
    Next n
  End If
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(UBound(O), 8) = O
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub


Then run the ReorgData macro.
 
Upvote 0
Thank you for your responses!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
I think I represented my data poorly. The data found in Column A can contain a Letter and Numbers, like "A123" to "V2222". The minimum number of package sizes is 1, in which its only package size is "POA". <o:p></o:p>
<o:p> </o:p>
Here is an actual sample set highlighting products with 1 size:<o:p></o:p>
<TABLE style="WIDTH: 1.5in; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=144 u1:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>product<o:p></o:p>
Q187<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>size<o:p></o:p>
POA<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q188<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">100g<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">1g<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">25g<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">5g<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q190<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD></TR></TBODY></TABLE><o:p> </o:p>
Goal:<o:p></o:p>
<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=400 u1:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q187<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num>POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q188<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num>100g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">1g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">25g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">5g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num>POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q190<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num>POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR></TBODY></TABLE><o:p> </o:p>
Rick, your macro was able to go through all my data when the first row is labels (product/size), but I came upon products with a row full of POAs and data shifted. It mainly occurred when only 1 package size existed for a product. Here is what the above sample set looks like when I run your macro:<o:p></o:p>
<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=512 u1:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>Q187<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>100g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>1g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>25g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>5g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>POA<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q188<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">100g<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q190<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">POA<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR></TBODY></TABLE><o:p> </o:p>
hiker95, your macro was able to cleanly tranpose 6000 rows of data when the first row isn't labels (product/size), but if I select all 40,000 rows it crashes my excel spreadsheet. That could just be my computer memory issues or something. So your marco works perfectly if I do it in large sets, which is definitely better than one-by-one! <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt" id=_x0000_i1025 alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\Users\Mishi\AppData\Local\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o:p></o:p>
Also I need a macro to transpose a different column B, which will consist of numerical values (prices without the dollar sign, ex. "1000" "200" "23.35" to "0"). I tried hiker95's macro, but it would say, "subscript out of range". Rick's macro could work, but the data still shifts.<o:p></o:p>
<o:p> </o:p>
Sample Data set with Price as Column B:<o:p></o:p>
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=128 u1:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>product<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64>
price<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q187<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q188<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
15.24<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
500<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
200<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">Q190<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" u1:num>
0<o:p></o:p>​
</TD></TR></TBODY></TABLE><o:p> </o:p>
Output of Rick's macro:<o:p></o:p>
<TABLE style="WIDTH: 5.5in; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=528 u1:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q187<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num u1:num>0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
0<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
0<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
15.24<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
500<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
200<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u1:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q188<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num u1:num>0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q189<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num u1:num>0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 60pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=80>Q190<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=64 u2:num u1:num>0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">X<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in"><o:p> </o:p>
</TD></TR></TBODY></TABLE>
 
Upvote 0
usernamem14 said:
Here is an actual sample set highlighting products with 1 size:
product size
Q187 POA
Q188 POA
Q189 100g
Q189 1g
Q189 25g
Q189 5g
Q189 POA
Q190 POA

Goal:
Q187 POA
Q188 100g 1g 25g 5g
Q189 POA
Q190 POA
Click to expand...
Your posted Goal is not correct, is it? Should it look like this instead?

Q187 POA
Q188 POA
Q189 100g 1g 25g 5g POA
Q190 POA

If so, then give this macro a try (if not, then I don't understand your rules for transposing the data)...

Code: 
Sub TransposeData()
  Dim X As Long, LastRow As Long, StartRow As Long
  StartRow = 2
  Application.ScreenUpdating = False
  Columns("B").Insert
  Range("B2").Value = "X"
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow
    If Cells(X, "A").Value <> Cells(X + 1, "A") Then
      Cells(StartRow, "C").Resize(, X - StartRow + 1) = WorksheetFunction.Transpose(Cells(StartRow, "C").Resize(X - StartRow + 1))
      StartRow = X + 1
      Cells(StartRow, "B").Value = "X"
    End If
  Next
  On Error Resume Next
  Range("B2").Resize(LastRow - 1).SpecialCells(xlBlanks).EntireRow.Delete
  Columns("B").Delete
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Rick, you're right about my intended goal, and it looks like you understand what I need because it worked perfectly! Thank you so much for your extra efforts. :)

Best,
Michelle
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
121
Peter_SSs
Peter_SSs
C
  • Question Question
Subtotal positive and negative values on visible rows
Replies
6
Views
246
Peter_SSs
Peter_SSs
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
127
Kaper
Kaper
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
73
Fluff
Fluff

Forum statistics

Threads
1,224,608
Messages
6,179,872
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top