advanced paste special transpose
 

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

advanced paste special transpose
 

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

"Matt" wrote:

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

advanced paste special transpose
 

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



"Matt" wrote:

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