Fill Formula please

[palups]

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.

 

[Jacob Skaria]

Select data range ColA to ColD. Data>Sort> 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]

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 click>PasteSpecial>select 'Formulas'>OK

If this post helps click Yes

 

[palups]

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.

 

[palups]

thanks jacob, this suggestion is working fine with me.

thanks again..

 

[Jacob Skaria]

--Did you sort data? Read the 1st post again. ColA to ColD. Data>Sort>
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