pulling data in rows!

[via135]

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

 

[Teethless mama]

"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(INDIRECT("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(RngA=$A2,ROW(INDIRECT("1:"&ROWS(RngB)))),COLUMNS($B:B))))

ctrl+shift+enter, not just enter
copy across and down

 

[ryguy7272]

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---

 

[yshridhar]

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]

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







- Show quoted text -

hi
ryguy7272

i am getting error "subscript out of range".!
can u guide me pl?

-via135

 

[via135]

hi
ryguy7272

i am getting error "subscript out of range".!
can u guide me pl?

-via135- Hide quoted text -

- Show quoted text -

???

-via135