Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste special transpose problem | Excel Discussion (Misc queries) | |||
Transpose, Paste Special | Excel Discussion (Misc queries) | |||
Paste Special / transpose | Excel Discussion (Misc queries) | |||
When I special paste and transpose.... | New Users to Excel | |||
Paste Special - Values - Transpose | Excel Worksheet Functions |