Extract or filter Data

[Cathrine Paul]

I have a large downloaded drilled down data.
A column contains its group numbers.its a raw format.
A column includes unique main group numbers with its sub group numbers.
its downloaded data from another application. so its contain unwanted numbers also.
For ex: group numbers stared with 045/001 instead of 001. but in my result only i need numbers as 001 ( without 045/) and its sub group also the same.
A column contains unwanted spaces also.
B and C columns contains its related data.

I want to filter this data with what are the main numbers i entered in D column.

For Ex: I have 99 main groups numbers and its sub group numbers
main group like this 001,002,... 099. (3 Digits) and its sub group 001001001,001001003 like this (9 digits ). (including 045/).

if i enter values in D column with 001,002,007,010,099. the result must be 001 and its sub,002 and its sub, 007 and its sub,010 and its sub,099 and its sub.
For more details pls refer the attached file

Pls help me.. thanks in advance.

[alansidman]

I have created your expected results in the attached file. I used Power Query/Get and Transform which is part of your Excel 2016. Here is the Mcode for this

Please Login or Register  to view this content.

click on the links in my signature to understand how to use this code. The file is attached.

[Cathrine Paul]

Thank you.. Alansidman..
Could you pls do with this in excel formula.
Some times I need this with older excel version too.

[JeteMc]

This proposed formula alternative employs a helper column which may be moved and/or hidden for aesthetic purposes.
The helper column (E) is populated using: =COUNTIF(D$2:D$3,VALUE(MID(A2,SEARCH("/",A2)+1,3)))
The 'Result' table (columns G:I) is populated using: =IFERROR(INDEX(A$2:A$25,AGGREGATE(15,6,(ROW(A$2:A$25)-ROW(A$1))/($E$2:$E$25=1),ROWS(A$1:A1))),"")
Let us know if you have any questions.

[Cathrine Paul]

an i lookup name with value in filter column (D1).? In this report D1 is validation list and I have a table with Value and Name for lookup (K2:K5 = Value L2:L5 = Name ). I need to add name with D1 Value from that table. It would be very helpful to me. Thanks in advance. Have a blessed day..

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

[Cathrine Paul]

Some users are using old version of excel. It would be nice if it is working with excel formulas.

[JeteMc]

Please upload an Excel file (not a picture) as was done in post #1 so that we can have a better understanding of what you want.
Let us know if you have any questions.

[Cathrine Paul]

K1:L5

Group No Group Name
001 Bakery
002 vegetables and fruits
005 Ice cream
008 Milk



D2 = Dropdown Data Validation List
D2 = Grou Number with GroupName
D2 Example 001_Bakery

[JeteMc]

Assuming that there should also be drop downs in D3:D5
1. Add a column M populated using: =K2&"_"&L2
2. Modify the formula** in E2 and down: =SUM(--IFERROR(--LEFT(D$2:D$5,3)=VALUE(MID(A2,SEARCH("/",A2)+1,3)),0))>0
** Denotes an array entered formula which is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
3. Modify the formula in columns G:I =IFERROR(INDEX(A$2:A$25,AGGREGATE(15,6,(ROW(A$2:A$25)-ROW(A$1))/($E$2:$E$25=TRUE),ROWS(A$1:A1))),"")
Let us know if you have any questions.

[Cathrine Paul]

Thank You.. Very Much.. It is very helpful..

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.