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

"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

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