VLook up with several conditions

[lorch]

Hi,

I'm receiving an extract of data and I would like to rearrange in another spreadsheet.

for that I have a to use a Vlookup function with several condition.

Here is the file I receive:
A B C D ...

-- removed inline image ---

<table border="0" cellpadding="0" cellspacing="0" width="956"><colgroup><col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:5632;width:116pt" width="154"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:4864;width:100pt" width="133"> <col style="mso-width-source:userset;mso-width-alt:5961;width:122pt" width="163"> <col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:6290;width:129pt" width="172"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;width:89pt" height="17" width="118">number simplified</td> <td class="xl63" style="border-left:none;width:116pt" width="154">NUMBER</td> <td class="xl63" style="border-left:none;width:51pt" width="68">R</td> <td class="xl63" style="border-left:none;width:100pt" width="133">ID</td> <td class="xl63" style="border-left:none;width:122pt" width="163"> start date</td> <td class="xl63" style="border-left:none;width:111pt" width="148"> end date</td> <td class="xl63" style="border-left:none;width:129pt" width="172"> status</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">00041782</td> <td class="xl63" style="border-top:none;border-left:none">00041782-X</td> <td class="xl63" style="border-top:none;border-left:none">A.12</td> <td class="xl63" style="border-top:none;border-left:none">OWF</td> <td class="xl64" style="border-top:none;border-left:none" align="right">22/05/2011</td> <td class="xl64" style="border-top:none;border-left:none" align="right">22/05/2011
</td> <td class="xl63" style="border-top:none;border-left:none">wwww</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">00041782</td> <td class="xl63" style="border-top:none;border-left:none">00041782-Y</td> <td class="xl63" style="border-top:none;border-left:none">A</td> <td class="xl63" style="border-top:none;border-left:none">3DWF</td> <td class="xl64" style="border-top:none;border-left:none" align="right">23/05/2011</td> <td class="xl64" style="border-top:none;border-left:none" align="right">23/05/2011
</td> <td class="xl63" style="border-top:none;border-left:none">xxx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">00041782</td> <td class="xl63" style="border-top:none;border-left:none">00041782-X</td> <td class="xl63" style="border-top:none;border-left:none">A</td> <td class="xl63" style="border-top:none;border-left:none">2DWF</td> <td class="xl64" style="border-top:none;border-left:none" align="right">25/05/2011</td> <td class="xl64" style="border-top:none;border-left:none" align="right">25/05/2011
</td> <td class="xl63" style="border-top:none;border-left:none">yyy</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">00041782</td> <td class="xl63" style="border-top:none;border-left:none">00041782-Y</td> <td class="xl63" style="border-top:none;border-left:none">B</td> <td class="xl63" style="border-top:none;border-left:none">3DWF</td> <td class="xl64" style="border-top:none;border-left:none" align="right">23/05/2011</td> <td class="xl64" style="border-top:none;border-left:none" align="right">23/05/2011</td> <td class="xl63" style="border-top:none;border-left:none">zzz</td> </tr> </tbody></table>

Here is the file I need to feed:

-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" width="779"><col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:92pt" height="17" width="123">number simplified</td> <td class="xl65" style="width:48pt" width="64">R</td> <td class="xl65" style="border-left:none;width:56pt" width="75">ID</td> <td class="xl65" style="border-left:none;width:48pt" width="64">status
</td> <td class="xl65" style="border-left:none;width:56pt" width="74">/start date</td> <td class="xl65" style="border-left:none;width:62pt" width="83">end date</td> <td class="xl65" style="border-left:none;width:56pt" width="75">ID</td> <td class="xl65" style="border-left:none;width:48pt" width="64">status</td> <td class="xl65" style="border-left:none;width:57pt" width="76">start date</td> <td class="xl65" style="border-left:none;width:61pt" width="81">end date</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">00041782</td> <td class="xl67" style="border-top:none;border-left:none">A</td> <td class="xl67" style="border-top:none;border-left:none">OWF</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none"> </td> <td class="xl67" style="border-top:none;border-left:none">3DWF</td> <td class="xl66" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

I want to have the status (contained in the data I receive) in the cell. the conditions are the blue cells:number 00041782 with R contains A and ID=OWF

I tried this formula bu I get a NA#:
{INDEX(Extract!G:G;(MATCH(1;(A2=Extract!A:A)*(B2=Extract!C:C)*(C2=Extract!D:D);0)))}

I have then to do the same for the start date, end date and change of conditions ID=3DWF but still with R=A and the ID=00041782.

If someone could help me it would be so great.

(Sorry for the visualization but the pictures I pasted first are not visible posts)

Thanks a lot in advance

 

[venkat1926]

I tried to have a formula. will u like to have a macaro. i hope you know how to run the macro

sheet1 is like this . I have added one more number in column A with some false data for writing macro and testing.

Excel Workbook
	A	B	C	D	E	F	G
1	number simplified	NUMBER	R	ID	start date	end date	status
2	41782	00041782-X	A.12	OWF	22/05/2011	22/05/2011	www
3	41782	00041782-Y	A	3DWF	23/05/2011	23/05/2011	xxx
4	41782	00041782-X	A	2DWF	25/05/2011	25/05/2011	yyy
5	41782	00041782-Y	B	3DWF	23/05/2011	23/05/2011	zzz
6	41783	111111	c	ert	23/5/2011	23/05/2011	ggg
Sheet1

now try this macro "test"

Sub test()
Dim number As Range, r As Range, un As Range, cun As Range
Dim j As Integer, k As Integer, rbg As Range, x, r2 As Range
Dim m As Integer
With Worksheets("sheet1")
Set number = Range(.Range("A1"), .Range("A1").End(xlDown))
Set r = .Range("A1").CurrentRegion
Set un = .Range("A1").End(xlDown).Offset(5, 0)
number.AdvancedFilter xlFilterCopy, , un, True
Set un = Range(un.Offset(1, 0), un.End(xlDown))
For Each cun In un
x = cun.Value
j = WorksheetFunction.CountIf(number, x)
With Worksheets("sheet2")
Set r2 = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
r2 = x
End With
r.AutoFilter field:=1, Criteria1:=x
m = number.Cells.Find(what:=x, lookat:=xlWhole).Row
'msgbox m
For k = m To m + j - 1

Set rbg = Range(.Cells(k, "C"), .Cells(k, "G"))
'msgbox rbg.Address
rbg.Copy
With Worksheets("sheet2")
.Cells(r2.Row, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial
End With
Next k
r.AutoFilter
Next cun
End With
End Sub

Sub undo()
With Worksheets("sheet2")
.Cells.Clear
End With
With Worksheets("sheet1")
Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End Sub

 

[lorch]

Thanks a lot.

Regarding the future users of my files, I'm not sure a macro is the best solution. But I'll try it.

I finally succeeded to find an equation but it is not working in all cases.
Here it is:
{INDEX(Extract!G:G;(MATCH(1;(A2=Extract!A:A)*(B2=Extract!C:C)*(C2=Extract!D:D);0)))}

The bolded piece of sentence is where I still have an issue.
I would like to have the letter A as a condition but not as the exact value, but the text containing letter A.

Do you have any idea?

I'm really sorry I'm sure your macro do it!!!

Many thanks

 

[Marcelo Branco]

Try

=INDEX(Extract!G2:G100,MATCH(1,(ISNUMBER(SEARCH(A2,Extract!A2:A100)))*(B2=Extract!C2:C100)*(C2=Extract!D2:D100),0))

Ctrl+Shift+Enter

I changed the references to specific ranges (adjust to suit) because references to entire columns in array-formulas cause a bad performance.

HTH

M

 

[lorch]

I tried but I'm still getting an NA with the formula:
{=INDEX(Extract!G2:G100;MATCH(1;(ISNUMBER(SEARCH(A2;Extract!A2:A100)))*(B2=Extract!C2:C100)*(C2=Extract!D2:D100);0))}

Is it the ISNUMBER and SEARCH formula which are not fitting?

 

[Aladin Akyurek]

I tried but I'm still getting an NA with the formula:
{=INDEX(Extract!G2:G100;MATCH(1;(ISNUMBER(SEARCH(A2;Extract!A2:A100)))*(B2=Extract!C2:C100)*(C2=Extract!D2:D100);0))}

Is it the ISNUMBER and SEARCH formula which are not fitting?

SEARCH looks for a partial string.

If such is not involved (hard to judge from your description), control+shift+enter, not just enter:

=INDEX(Extract!G2:G100;MATCH(1;IF(A2=Extract!A2:A100;IF(B2=Extract!C2:C100,IF(C2=Extract!D2:D100;1)));0))

 

[Marcelo Branco]

I tried but I'm still getting an NA with the formula:
{=INDEX(Extract!G2:G100;MATCH(1;(ISNUMBER(SEARCH(A2;Extract!A2:A100)))*(B2=Extract!C2:C100)*(C2=Extract!D2:D100);0))}

Is it the ISNUMBER and SEARCH formula which are not fitting?

Sorry, i used ISNUMBER(SEARCH(... in the wrong column

Try this

=INDEX(Extract!G2:G100;MATCH(1;(ISNUMBER(SEARCH(A2;Extract!A2:A100)))*(ISNUMBER(SEARCH(B2;Extract!C2:C100)))*(C2=Extract!D2:D100);0))

M.

 

[lorch]

Try this

=INDEX(Extract!G2:G100;MATCH(1;(ISNUMBER(SEARCH(A2;Extract!A2:A100)))*(ISNUMBER(SEARCH(B2;Extract!C2:C100)))*(C2=Extract !D2:D100);0))

M.

Well now I get in the cell: TRUE

Is there a way to add a file so you could have a better view of the issue?

 

[lorch]

Sorry don't know what happened but it is working now!!!!

Fantastic, it help me so much!!!!!


Many many thanksray:

 

[Aladin Akyurek]

...
Is there a way to add a file so you could have a better view of the issue?

Much easier to specify...

1. the values to look for (and the cells where they are entered),

2. the ranges in which the values in (1) must be looked for,

3. and the range where the result must come from.