"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