| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill Formula please
Hello,
Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fill Formula please
Select data range ColA to ColD. DataSort criteria1 ColB Ascending
criteria2 ColC ascending. This will group ColB. Now 1. Select the data range in ColC (say C1:C100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fill Formula please
If you dont want to sort the data try the below. I tried the below with your
sample data --In cell C2 enter the formula and copy the cell =OFFSET($B$1,MATCH(B2,B:B,0)-1,1) --Keeping the copy select Col C --Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK --Right clickPasteSpecialselect 'Formulas'OK If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fill Formula please
Thanks for the reply,i've noticed that the last row ColB 54321's Col C value
is = to Col C 54672 C value were its not should be the case. I just want to fill Col C with given value that have equal value in Col B. e.i. Value in Col is Given already, All 54321 must have a Col C value of 1, 65743 has a Col C value of 5, 65789 has 34, 43567 has 456, and 54672 has 321, im refering to Col B with the 5 digit numbers. Thank you. "Jacob Skaria" wrote: Select data range ColA to ColD. DataSort criteria1 ColB Ascending criteria2 ColC ascending. This will group ColB. Now 1. Select the data range in ColC (say C1:C100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fill Formula please
thanks jacob, this suggestion is working fine with me.
thanks again.. "Jacob Skaria" wrote: If you dont want to sort the data try the below. I tried the below with your sample data --In cell C2 enter the formula and copy the cell =OFFSET($B$1,MATCH(B2,B:B,0)-1,1) --Keeping the copy select Col C --Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK --Right clickPasteSpecialselect 'Formulas'OK If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fill Formula please
--Did you sort data? Read the 1st post again. ColA to ColD. DataSort
criteria1 ColB Ascending criteria2 ColC ascending. This will group ColB. --OR else try the second solution which do not require a sort If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Thanks for the reply,i've noticed that the last row ColB 54321's Col C value is = to Col C 54672 C value were its not should be the case. I just want to fill Col C with given value that have equal value in Col B. e.i. Value in Col is Given already, All 54321 must have a Col C value of 1, 65743 has a Col C value of 5, 65789 has 34, 43567 has 456, and 54672 has 321, im refering to Col B with the 5 digit numbers. Thank you. "Jacob Skaria" wrote: Select data range ColA to ColD. DataSort criteria1 ColB Ascending criteria2 ColC ascending. This will group ColB. Now 1. Select the data range in ColC (say C1:C100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, Need help, just wondering how to fill colomn C using vlookup,match,index, etc. suppose colomn B has a value of 54321 and with colomn C value of 1 given, I want to fill all C colomn with "1" which have a value of 54321 in colomn B, and so on... A...............B.............C..................D ashish 54321 1 suresh ashish 54321 suresh ashish 54321 suresh sanjay 65743 5 sanjay sanjay 65743 sanjay pongal 65789 34 rajesh pongal 65789 rajesh pongal 65789 rajesh ashish 54321 pongal rajesh 43567 456 ashish rajesh 43567 ashish rajesh 43567 ashish rajesh 43567 ashish suresh 54672 321 ashish suresh 54672 ashish suresh 54672 ashish suresh 54672 ashish ashish 54321 ashish thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula fill down | Excel Discussion (Misc queries) | |||
| fill column with formula | Excel Worksheet Functions | |||
| Formula Fill help | Excel Worksheet Functions | |||
| Fill Down formula Help Please | Excel Discussion (Misc queries) | |||
| Help with formula fill down | Excel Discussion (Misc queries) | |||
Linear Mode
