advanced paste special transpose

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I have some data in the following format:
TTL VAL
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
and i want it into this format...
A 123
B 123
C 123

any suggestions. the only reason this is so difficult is becasue i need to
do it a formula as i have over 300 000 records over 4 different excel
worksheets.

Thnx

-Matt
 
Sub combine_rows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then
Range("B" & RowCount) = Trim(Range("B" & RowCount)) & _
Trim(Range("B" & (RowCount + 1)))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop


End Sub
 
In D2:
=IF(ISERR(SMALL(IF(MATCH(TTL,TTL,0)=ROW(INDIRECT("1:"&ROWS(TTL))),MATCH(TTL,TTL,0)),ROWS($1:1))),"",INDEX(TTL,SMALL(IF(MATCH(TTL,TTL,0)=ROW(INDIRECT("1:"&ROWS(TTL))),MATCH(TTL,TTL,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

In E2:
=IF(ISERR(SMALL(IF(TTL=$D2,ROW(INDIRECT("1:"&ROWS(VAL)))),COLUMNS($E:E))),"",INDEX(VAL,SMALL(IF(TTL=$D2,ROW(INDIRECT("1:"&ROWS(VAL)))),COLUMNS($E:E))))

ctrl+shift+enter, not just enter
copy across and down as far as needed
 
Back
Top