Forum Discussion
Solved
Extract data using a second list
Hello,
I am newbie with Excel and I have been struggling with this problem.
I have a first table with multiple variables in line B-E all describing different aspects of line A.
In line G, I have a list where some of the values correspond to those in A, others not.
I want to extract the data from line A having a pair from line G, this by also preserving their descriptive variables in B-E. I want to exclude those not having a pair.
Does someone a
Hi tw1211,
Use the FILTER function.
The FILTER function allows you to filter a table based on a set of criteria. To use the FILTER function to extract data using a second list, follow these steps:
- Create a new column next to your second list and call it "Match".
- In cell H2, enter the following formula:
=FILTER(A:E, A:A=G2)This formula will return all of the rows from the first table (columns A:E) where the value in column A matches the value in cell G2.
Copy the formula down to the rest of the cells in column H.
Select the entire column H and copy it.
Paste the copied data into a new worksheet.
This will create a new worksheet with only the data from the first table that has a corresponding value in the second list.
Here is an example:
First table: A | B | C | D | E -- | -- | -- | -- | -- Product 1 | Price | Quantity | Color | Weight Product 2 | Price | Quantity | Color | Weight Product 3 | Price | Quantity | Color | Weight Product 4 | Price | Quantity | Color | Weight Product 5 | Price | Quantity | Color | Weight Second list: G -- Product 1 Product 3
- Create a new column next to the second list (column G) and call it "Match".
- In cell H2, enter the following formula:
=FILTER(A:E, A:A=G2)Copy the formula down to the rest of the cells in column H.
Select the entire column H and copy it.
Paste the copied data into a new worksheet.
The new worksheet will contain the following data:
A | B | C | D | E -- | -- | -- | -- | -- Product 1 | Price | Quantity | Color | Weight Product 3 | Price | Quantity | Color | Weight
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic (LinkedIn)
Hi tw1211,
Use the FILTER function.
The FILTER function allows you to filter a table based on a set of criteria. To use the FILTER function to extract data using a second list, follow these steps:
- Create a new column next to your second list and call it "Match".
- In cell H2, enter the following formula:
=FILTER(A:E, A:A=G2)This formula will return all of the rows from the first table (columns A:E) where the value in column A matches the value in cell G2.
Copy the formula down to the rest of the cells in column H.
Select the entire column H and copy it.
Paste the copied data into a new worksheet.
This will create a new worksheet with only the data from the first table that has a corresponding value in the second list.
Here is an example:
First table: A | B | C | D | E -- | -- | -- | -- | -- Product 1 | Price | Quantity | Color | Weight Product 2 | Price | Quantity | Color | Weight Product 3 | Price | Quantity | Color | Weight Product 4 | Price | Quantity | Color | Weight Product 5 | Price | Quantity | Color | Weight Second list: G -- Product 1 Product 3
- Create a new column next to the second list (column G) and call it "Match".
- In cell H2, enter the following formula:
=FILTER(A:E, A:A=G2)Copy the formula down to the rest of the cells in column H.
Select the entire column H and copy it.
Paste the copied data into a new worksheet.
The new worksheet will contain the following data:
A | B | C | D | E -- | -- | -- | -- | -- Product 1 | Price | Quantity | Color | Weight Product 3 | Price | Quantity | Color | Weight
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic (LinkedIn)
Thank you LeonPavesic, it worked!
Now the problem I encounter when copy/pasting my new data into a new worksheet is that the following message comes up for the line corresponding to the line H: =FILTER(#REF!;#REF! =#REF!;0)
The other variables also disappear.
What should I do?
Hi tw1211,
thanks for the update.I'm glad to hear that the FILTER function worked for you.
The message =FILTER(#REF!;#REF! =#REF!;0) is appearing because the FILTER function is unable to find the table that you are referencing in the formula. This can happen for a few reasons, such as:
- You have accidentally deleted the table.
- You have moved the table to a different worksheet.
- You have renamed the table.
To fix this problem, make sure that the table that you are referencing in the FILTER function still exists and that it has the same name. You can also try copying and pasting the FILTER formula into a new cell.
If you are still having problems, you can try using the following formula:
=FILTER(INDIRECT("A:E"),A:A=G2)This formula uses the INDIRECT function to convert the text string "A:E" into a cell reference. This means that the formula will always reference the first table, even if you move it or rename it.
Here are the steps on how to use the INDIRECT function:
- In cell H2, enter the following formula:
=FILTER(INDIRECT("A:E"),A:A=G2)- Copy the formula down to the rest of the cells in column H.
- Select the entire column H and copy it.
- Paste the copied data into a new worksheet.
The new worksheet should contain the data from the first table that has a corresponding value in the second list.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic (LinkedIn)
