Very Hard Task - Updated Data

[smartguy]

Hello All,

Good Day !!!!

I have excel file in the below format.

Excel Workbook
	A	B	C	D	E	F	G	H
1	sd	me	eq	sn	Item	ht	V1	L2
2	12345	M1	*	*	10	0	M1	*
3	12345	M2	*	*	20	10	M2	*
4	12345	ag1	E1	s1	190	10	*	*
5	12345	ag2	E2	s2	200	10	*	*
6	12345	ag3	E3	s3	210	20	*	*
7	12345	ag4	E4	s4	220	10	*	*
8	12345	ag5	E5	s5	230	10	*	*
9	12345	ag6	E6	s6	240	10	*	*
10	12345	ag7	E7	s7	250	10	*	*
Sheet1

I want to updated "L2" Data.

Criteria :

If "V1" IS blank i want to update "L2".

Based on "HT" i want to updated "L2"

Example :

If "HT" is "10" then i want to go to "Item" and find the "10" and updated "L2"

Answer :

Excel Workbook
	A	B	C	D	E	F	G	H
1	sd	me	eq	sn	Item	ht	V1	L2
2	12345	M1	*	*	10	0	M1	*
3	12345	M2	*	*	20	10	M2	*
4	12345	ag1	E1	s1	190	10	*	M1
5	12345	ag2	E2	s2	200	10	*	M1
6	12345	ag3	E3	s3	210	20	*	M2
7	12345	ag4	E4	s4	220	10	*	M1
8	12345	ag5	E5	s5	230	10	*	M1
9	12345	ag6	E6	s6	240	10	*	M1
10	12345	ag7	E7	s7	250	10	*	M1
Sheet1

 

[JackDanIce]

It is very unclear what you want updating

If "V1" IS blank i want to update "L2".

Update with what? You need to state your requirements with much more clarity

 

[smartguy]

Hello Sir,

Thanks for Reply.

IF"V1" is Blank i want to Update "L2" Value.

What value update in "L2" ?
Go to "HT" and find the no (Example "10") Then Go to "Item" Find the Value ("10") Then find the "me" Value for "10" and update the same in "L2".

i could be clear?

 

[smartguy]

Please Help to Provide the Solution...

 

[smartguy]

Please help on the below....

 

[gingersteve]

In H2:

=IF(G2="",INDEX(B:B,MATCH(F2,E:E,0)),"")

Then copy down the whole list.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG25May37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 6) = vbNullString [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Rng.Offset(, 4)
            [COLOR="Navy"]If[/COLOR] Rw = Dn.Offset(, 5) [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 7) = Rw.Offset(, 2)
                [COLOR="Navy"]Exit[/COLOR] For
             [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Rw
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Thanks Mr.Mickg

It's Working Fine....