| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pulling data in rows!
hi!
in sheet1, i am having codes (numerical or text) in col A and values in col B as under: COL A COL B abc 10 abc -10 1111 20 2222 30 1111 -40 ab123 50 ab123 60 2222 -10 abc 20 now what i want is in sheet2 codes in COL A and values in COL B , COL C, COLD ..etc in row wise as under: COL A COL B COL C COL D abc 10 -10 20 1111 20 -40 2222 30 -10 ab123 50 60 any help pl? thks -via135 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pulling data in rows!
"RngA" and "RngB" are defined name ranges in ColA and ColB
Sheet 2: to get unique values A2: =IF(ISERR(SMALL(IF(MATCH(RngA,RngA,0)=ROW(INDIRECT ("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),ROWS($1:1)) ),"",INDEX(RngA,SMALL(IF(MATCH(RngA,RngA,0)=ROW(IN DIRECT("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),ROWS( $1:1)))) ctrl+shift+enter, not just enter copy down B2: =IF(ISERR(SMALL(IF(RngA=$A2,ROW(INDIRECT("1:"&ROWS (RngB)))),COLUMNS($B:B))),"",INDEX(RngB,SMALL(IF(R ngA=$A2,ROW(INDIRECT("1:"&ROWS(RngB)))),COLUMNS($B :B)))) ctrl+shift+enter, not just enter copy across and down "via135" wrote: hi! in sheet1, i am having codes (numerical or text) in col A and values in col B as under: COL A COL B abc 10 abc -10 1111 20 2222 30 1111 -40 ab123 50 ab123 60 2222 -10 abc 20 now what i want is in sheet2 codes in COL A and values in COL B , COL C, COLD ..etc in row wise as under: COL A COL B COL C COL D abc 10 -10 20 1111 20 -40 2222 30 -10 ab123 50 60 any help pl? thks -via135 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pulling data in rows!
Here is a macro that will do the same thing:
Sub newlist() Set w1 = Sheets("Combine Multiple Entries1") Set w2 = Sheets("Combine Multiple Entries2") w2.Cells(1, 1).Value = w1.Cells(1, 1).Value w2.Cells(1, 2).Value = w1.Cells(1, 2).Value Ide = Cells(1, 1).Value w1.Activate n = Cells(Rows.count, 1).End(xlUp).Row k = 3 kk = 1 For i = 2 To n If w1.Cells(i, 1).Value = Ide Then w2.Cells(kk, k).Value = w1.Cells(i, 2).Value k = k + 1 Else kk = kk + 1 k = 3 Ide = w1.Cells(i, 1).Value w2.Cells(kk, 1).Value = Ide w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value End If Next End Sub Regards, Ryan--- -- RyGuy "Teethless mama" wrote: "RngA" and "RngB" are defined name ranges in ColA and ColB Sheet 2: to get unique values A2: =IF(ISERR(SMALL(IF(MATCH(RngA,RngA,0)=ROW(INDIRECT ("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),ROWS($1:1)) ),"",INDEX(RngA,SMALL(IF(MATCH(RngA,RngA,0)=ROW(IN DIRECT("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),ROWS( $1:1)))) ctrl+shift+enter, not just enter copy down B2: =IF(ISERR(SMALL(IF(RngA=$A2,ROW(INDIRECT("1:"&ROWS (RngB)))),COLUMNS($B:B))),"",INDEX(RngB,SMALL(IF(R ngA=$A2,ROW(INDIRECT("1:"&ROWS(RngB)))),COLUMNS($B :B)))) ctrl+shift+enter, not just enter copy across and down "via135" wrote: hi! in sheet1, i am having codes (numerical or text) in col A and values in col B as under: COL A COL B abc 10 abc -10 1111 20 2222 30 1111 -40 ab123 50 ab123 60 2222 -10 abc 20 now what i want is in sheet2 codes in COL A and values in COL B , COL C, COLD ..etc in row wise as under: COL A COL B COL C COL D abc 10 -10 20 1111 20 -40 2222 30 -10 ab123 50 60 any help pl? thks -via135 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pulling data in rows!
Taking in column C the codes
put in D2 =INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$C2,ROW($B$2 :$B$10)-MIN(ROW($B$2:$B$10))+1),D$1)) enter in D1 = 1; E1 =2 and F1 = 3 Copy the formula from d2:f5 . modify the ranges according to your data. It is an array formula. Enter with CSE. Best wishes Sreedhar "via135" wrote: hi! in sheet1, i am having codes (numerical or text) in col A and values in col B as under: COL A COL B abc 10 abc -10 1111 20 2222 30 1111 -40 ab123 50 ab123 60 2222 -10 abc 20 now what i want is in sheet2 codes in COL A and values in COL B , COL C, COLD ..etc in row wise as under: COL A COL B COL C COL D abc 10 -10 20 1111 20 -40 2222 30 -10 ab123 50 60 any help pl? thks -via135 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pulling data in rows!
On May 2, 9:41*am, ryguy7272
wrote: Here is a macro that will do the same thing: Sub newlist() Set w1 = Sheets("Combine Multiple Entries1") Set w2 = Sheets("Combine Multiple Entries2") w2.Cells(1, 1).Value = w1.Cells(1, 1).Value w2.Cells(1, 2).Value = w1.Cells(1, 2).Value Ide = Cells(1, 1).Value w1.Activate n = Cells(Rows.count, 1).End(xlUp).Row k = 3 kk = 1 For i = 2 To n If w1.Cells(i, 1).Value = Ide Then w2.Cells(kk, k).Value = w1.Cells(i, 2).Value k = k + 1 Else kk = kk + 1 k = 3 Ide = w1.Cells(i, 1).Value w2.Cells(kk, 1).Value = Ide w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value End If Next End Sub Regards, Ryan--- -- RyGuy "Teethless mama" wrote: "RngA" and "RngB" are defined name ranges in ColA and ColB Sheet 2: to get unique values A2: =IF(ISERR(SMALL(IF(MATCH(RngA,RngA,0)=ROW(INDIRECT ("1:"&ROWS(RngA))),MATCH(*RngA,RngA,0)),ROWS($1:1) )),"",INDEX(RngA,SMALL(IF(MATCH(RngA,RngA,0)=ROW(I N*DIRECT("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),ROW S($1:1)))) ctrl+shift+enter, not just enter copy down B2: =IF(ISERR(SMALL(IF(RngA=$A2,ROW(INDIRECT("1:"&ROWS (RngB)))),COLUMNS($B:B)))*,"",INDEX(RngB,SMALL(IF( RngA=$A2,ROW(INDIRECT("1:"&ROWS(RngB)))),COLUMNS($ B*:B)))) ctrl+shift+enter, not just enter copy across and down "via135" wrote: hi! in sheet1, *i am having codes (numerical or text) in col A and values in col B as under: COL A * * * * *COL B abc * * * * * * * 10 abc * * * * * * * *-10 1111 * * * * * * 20 2222 * * * * * * 30 1111 * * * * * * -40 ab123 * * * * * *50 ab123 * * * * * *60 2222 * * * * * * *-10 abc * * * * * * * *20 now what i want is in sheet2 codes in COL A and *values in COL B , COL *C, COLD ..etc in row wise as under: COL A * *COL B * * *COL C * * * * COL D *abc * * * * 10 * * * * * *-10 * * * * * * * 20 1111 * * * *20 * * * * * *-40 2222 * * * *30 * * * * * *-10 ab123 * * * 50 * * * * * * 60 any help pl? thks -via135- Hide quoted text - - Show quoted text - hi ryguy7272 i am getting error "subscript out of range".! can u guide me pl? -via135 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pulling data in rows!
On May 3, 7:31*am, via135 wrote:
On May 2, 9:41*am, ryguy7272 wrote: Here is a macro that will do the same thing: Sub newlist() Set w1 = Sheets("Combine Multiple Entries1") Set w2 = Sheets("Combine Multiple Entries2") w2.Cells(1, 1).Value = w1.Cells(1, 1).Value w2.Cells(1, 2).Value = w1.Cells(1, 2).Value Ide = Cells(1, 1).Value w1.Activate n = Cells(Rows.count, 1).End(xlUp).Row k = 3 kk = 1 For i = 2 To n If w1.Cells(i, 1).Value = Ide Then w2.Cells(kk, k).Value = w1.Cells(i, 2).Value k = k + 1 Else kk = kk + 1 k = 3 Ide = w1.Cells(i, 1).Value w2.Cells(kk, 1).Value = Ide w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value End If Next End Sub Regards, Ryan--- -- RyGuy "Teethless mama" wrote: "RngA" and "RngB" are defined name ranges in ColA and ColB Sheet 2: to get unique values A2: =IF(ISERR(SMALL(IF(MATCH(RngA,RngA,0)=ROW(INDIRECT ("1:"&ROWS(RngA))),MATCH(**RngA,RngA,0)),ROWS($1:1 ))),"",INDEX(RngA,SMALL(IF(MATCH(RngA,RngA,0)=ROW( I*N*DIRECT("1:"&ROWS(RngA))),MATCH(RngA,RngA,0)),R OWS($1:1)))) ctrl+shift+enter, not just enter copy down B2: =IF(ISERR(SMALL(IF(RngA=$A2,ROW(INDIRECT("1:"&ROWS (RngB)))),COLUMNS($B:B)))**,"",INDEX(RngB,SMALL(IF (RngA=$A2,ROW(INDIRECT("1:"&ROWS(RngB)))),COLUMNS( $*B*:B)))) ctrl+shift+enter, not just enter copy across and down "via135" wrote: hi! in sheet1, *i am having codes (numerical or text) in col A and values in col B as under: COL A * * * * *COL B abc * * * * * * * 10 abc * * * * * * * *-10 1111 * * * * * * 20 2222 * * * * * * 30 1111 * * * * * * -40 ab123 * * * * * *50 ab123 * * * * * *60 2222 * * * * * * *-10 abc * * * * * * * *20 now what i want is in sheet2 codes in COL A and *values in COL B , COL *C, COLD ..etc in row wise as under: COL A * *COL B * * *COL C * * * * COL D *abc * * * * 10 * * * * * *-10 * * * * * * * 20 1111 * * * *20 * * * * * *-40 2222 * * * *30 * * * * * *-10 ab123 * * * 50 * * * * * * 60 any help pl? thks -via135- Hide quoted text - - Show quoted text - hi ryguy7272 i am getting error "subscript out of range".! can u guide me pl? -via135- Hide quoted text - - Show quoted text - ??? -via135 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tying to extract all data from a pivot pulling from external data | Excel Discussion (Misc queries) | |||
| Linking two spreadsheet, pulling data from one cell to another, data is being truncated | Excel Worksheet Functions | |||
| Pulling data from multiple rows and columns | Excel Worksheet Functions | |||
| pulling data from one sheet and listing selected data in another | Excel Worksheet Functions | |||
| PULLING OUT CONSECUTIVE ROWS | Excel Discussion (Misc queries) | |||
Linear Mode
