pulling data in rows!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

via135 - 02 May 2008 02:42 GMT

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

Reply to this Message

Teethless mama - 02 May 2008 03:16 GMT

"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

> hi!
>
[quoted text clipped - 26 lines]
>
> -via135

Reply to this Message

ryguy7272 - 02 May 2008 05:41 GMT

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

Signature

RyGuy

> "RngA" and "RngB" are defined name ranges in ColA and ColB
>
[quoted text clipped - 43 lines]
> >
> > -via135

Reply to this Message

via135 - 03 May 2008 03:31 GMT

On May 2, 9:41 am, ryguy7272 <ryguy7...@discussions.microsoft.com>
wrote:

> Here is a macro that will do the same thing:
> Sub newlist()
[quoted text clipped - 76 lines]
>
> - Show quoted text -

hi
ryguy7272

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

-via135

Reply to this Message

via135 - 03 May 2008 18:44 GMT

> On May 2, 9:41 am, ryguy7272 <ryguy7...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 89 lines]
>
> - Show quoted text -

???

-via135

Reply to this Message

yshridhar - 02 May 2008 08:20 GMT

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

> hi!
>
[quoted text clipped - 26 lines]
>
> -via135

Reply to this Message