Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |