Retrieve data from an Excel table into another table.

[julietterichelieu]

Hello,

I would like to retrieve data from an Excel table into another table.

In my table I have to retrieve in column AX, the data according to two criteria in column E and in column F

If in column E there is "VMO" and in column F there is "EKZEK" then take the value in column "AX" on the same line and give me the value "215" in cells N14 for example

Is it possible??

Thanks in advance

 

[mumps]

Do "VMO" and "EKZEK" have to be in the same row in other words beside each other? In your example, that would be N13 not N14. Is this correct? If so, can there be more than one row that contain "VMO" and "EKZEK" next to each other?

 

[julietterichelieu]

no in the table I download which is like this the two criteria are on the same line
So i can't change and move "VMO" and "EKZEK" to two different line.

And yes sorry its in line 13

 

[mumps]

Try:

Sub retrieveData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("E2", Range("E" & Rows.Count).End(xlUp)).Resize(, 46).Value
    For i = 1 To UBound(v)
        If v(i, 1) = "VMO" And v(i, 2) = "EKZEK" Then
            Range("N" & i + 1) = v(i, 46)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[julietterichelieu]

Thank you very much

But after i want to do it for column not only AX, and also for other line not only VMO and EKZEK

There is any formula possible ?

For example in line 15 : i want for VMO and ALK the value in colomn AK and BK and BF

I want this table paste in another one for different line and column but with 2 criteria

It work with the forumula : =VLOOKUP(N2,Sheet2!$F$2:$BL$49,45,FALSE)

But N2 is for example "ALK" and i want also the criteria "VMO". So i want if its "ALK" and "VMO" the data in column 45 and after if its VMO and "EKZEK" the data in column 45. And after the same but the data in column 46 .....

Its hard to explain by message... i don't know if its clear....

 

[mumps]

I'm sorry but it is not very clear. It is hard to work with pictures so it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[julietterichelieu]

Can i send you the file by email?

 

[mumps]

I would prefer not to share my email address. Please see the options in Post #6.

 

[julietterichelieu]

Yes i understand but i cant use the two website and use XL2BB as well..

I will send you picture with better explanation

 

[julietterichelieu]

As you can see there is 2 slides "Inventaire" and "data" and i want to take the value from the sheet "data" and put it in the table in the sheet "inventaire"

So in the sheet "Inventaire" colum "L" (D) and "M" (E) i want all the data from the sheet "data" column "L" and "M" who have the same "Code valeur" and are "VMOB" (and not CPN, this is why i can't use VLOOKUP and filter only with the "code vaeur", I need 2 filter : "Code valeur" and "VMOB"....

As you can see in red, if i filter only with the "code valeur" there is 2 differents number associated with the same one in column "L" and "M". I would normally used VLOOKUP but this is not possible, or index but i don't know how to use it.

I hope this is clear...

Thanks in advance