Match two row numric
 

hello,
is there anyway formula or macro for solving follow problam ? what i want just
B_col dat equally A-col like as under.
A_col B_col --- A_col B_col
1 1 1 1
2 2 2 2
3 4 3
4 6 4 4
5 5
6 6 6

Match two row numric
 

Say you enter this formula in C1 and copy down as needed:

=IF(ISNA(MATCH(A1,$B$1:$B$100,0)),"",INDEX($B$1:$B $100,
MATCH(A1,$B$1:$B$100,0)))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Tufail" wrote in message
...
hello,
is there anyway formula or macro for solving follow problam ? what i want
just
B_col dat equally A-col like as under.
A_col B_col --- A_col B_col
1 1 1 1
2 2 2 2
3 4 3
4 6 4 4
5 5
6 6 6

Match two row numric
 

Perhaps something like this:

If Col_B contains formulas that you need to keep intact....

Enter this ARRAY FORMULA* in C1:

=IF(ISNUMBER(B1),B1,INDEX(ROW($B$1:INDEX(B:B,MAX(B :B))),SMALL(IF(COUNTIF($B$1:INDEX(B:B,MAX(B:B)),RO W($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1)=0,ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1)-COUNT($B$1:B1))-MIN(B:B))+MIN(B:B))

That formula will listing the missing list numbers, starting in the cell to
the right of the first blank cell in Col_B. If a Col_B cell has a number,
that number is used in Col_C.

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

(Also, since text wrap is sure to impact the display, there are NO spaces in
that formula.)

Copy C1 into C2 and down as far as you need.

Then sort only columns B and C by Col_C

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tufail" wrote:

hello,
is there anyway formula or macro for solving follow problam ? what i want just
B_col dat equally A-col like as under.
A_col B_col --- A_col B_col
1 1 1 1
2 2 2 2
3 4 3
4 6 4 4
5 5
6 6 6

Match two row numric
 

yes working really very well, thank you very much.
tufail

"Ron Coderre" wrote:

Perhaps something like this:

If Col_B contains formulas that you need to keep intact....

Enter this ARRAY FORMULA* in C1:

=IF(ISNUMBER(B1),B1,INDEX(ROW($B$1:INDEX(B:B,MAX(B :B))),SMALL(IF(COUNTIF($B$1:INDEX(B:B,MAX(B:B)),RO W($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1)=0,ROW($B$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1)-COUNT($B$1:B1))-MIN(B:B))+MIN(B:B))

That formula will listing the missing list numbers, starting in the cell to
the right of the first blank cell in Col_B. If a Col_B cell has a number,
that number is used in Col_C.

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

(Also, since text wrap is sure to impact the display, there are NO spaces in
that formula.)

Copy C1 into C2 and down as far as you need.

Then sort only columns B and C by Col_C

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tufail" wrote:

hello,
is there anyway formula or macro for solving follow problam ? what i want just
B_col dat equally A-col like as under.
A_col B_col --- A_col B_col
1 1 1 1
2 2 2 2
3 4 3
4 6 4 4
5 5
6 6 6

Match two row numric
 

thank you very much it's working very well, tufail

"RagDyeR" wrote:

Say you enter this formula in C1 and copy down as needed:

=IF(ISNA(MATCH(A1,$B$1:$B$100,0)),"",INDEX($B$1:$B $100,
MATCH(A1,$B$1:$B$100,0)))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Tufail" wrote in message
...
hello,
is there anyway formula or macro for solving follow problam ? what i want
just
B_col dat equally A-col like as under.
A_col B_col --- A_col B_col
1 1 1 1
2 2 2 2
3 4 3
4 6 4 4
5 5
6 6 6