How to copy row data matching specific column criteria

[jazmarc]

I'm a beginning Excel user. Still learning terms. I want to copy row data that matches specific column data. 

The column data I want to match on is composed of Sales Contact names. The Sales contact names rarely change. The column header never changes, it's AK. I'm building a macro to copy data rows matching ONLY those Contact names I've listed in column AK.  

I'm familiar with building macros, but having trouble determining which Function(s) might best perform this job.

XLookup seems to be the best candidate. But Microsoft's examples don't fit the bill.

Do I need to create a lookup_array? Can anyone point me in direction with great Xlookup examples?

(If Xlookup is the way to go) Thank you,

I've provided sample data:

The names in column AK will change from day to day but the list of names is limited to 10 different known names. I've included an example list below. The function should be aware of this list.

I'll copy only the row data matching anyone in that list of 10 names.

In this example, I'd only want Annie MacDonald and Sam Jones. 

I want to copy these rows to another worksheet.

My list of 10 names:

Sam Jones

Annie MacDonald

Lucy Brown

Tom Fitter

Walt White

Sally Mcgee

Tonia Arnold

Lou Reed

Sammy Hagar

Warren Zevon

[#2]

@jazmarc 

Since you have not shares any sample data as well as expected output, therefore I've assumed the data set along with the criteria, and would like to show few method, that how could you extract record/records. 

How it works:

• Cell G10, G18 and G24 has criteria to match with related records.

Method 1:

• Extract multiple rows match the criteria in one column
• Enter this array (CSE) formula in cell F12, finish with Ctrl+Shift+Enter, and fill across.

{=IFERROR(INDEX($A$12:$D$21,SMALL(IF(COUNTIF($G$10,$C$12:$C$21),ROW($A$12:$D$21)-MIN(ROW($A$12:$D$21))+1),ROW(A1)),COLUMN(A1)),"")}

Method 2:

• Get one row match with the criteria.
• Formula in cell F21, finish with Enter, and fill Right.

=VLOOKUP($G$18,$A$12:$D$21,COLUMN(A1),FALSE)

Method 3:

• Get one row match with the criteria.
• Formula in cell F26, finish with Enter, and fill Right.
• This formula in performing REVERSE LOOKUP, since the LOOKUP value (the criteria) is not in first column.

=VLOOKUP($G$24,CHOOSE({1,2},$C$12:$C$21,A12:A21),2,0)

N.B.  Adjust cell references and criteria in formula as needed.

[#3]

Thank you for your help! I realized I was not clear with my need. I've added more information to my question. The Function needs to be aware of the 10 names that may or may not appear in the AK column.

[#4]

@jazmarc 

You need Method 1, with little modifications.

Use this array (CSE) formula where you need, finish with Ctrl+Shift+Enter, and fill across.

{=IFERROR(INDEX($D$4:$AK$13,SMALL(IF(COUNTIF($AK$1,$AK$4:$AK$13)+COUNTIF($AK$2,$AK$4:$AK$13),ROW($D$4:$AK$13)-MIN(ROW($D$4:$AK$13))+1),ROW(A1)),COLUMN(A1)),"")}

N.B. 

• Before you use this formula enter those TWO NAMEs in cell AK1 and AK2, you want extract records for them.
• Adjust cell references and NAMEs in AK1 & AK2 as needed.

[#5]

@jazmarc You may want to use a pivot table for this and filter by the names you are interested in.  You could even add a slicer to easily select those names.

Alternatively, if you have the up to date Excel with dynamic arrays you should have a function called FILTER() which can make this process relatively easier if you have something against pivot tables.  Basically you can use:

=FILTER(A:A,ISNUMBER(MATCH($AO:$AO,$CC:$CC)))

where I have the names listed in column CC (you could change that to a different column, different sheet, or defined name) 

and then copy that right for however many columns you need.

[#6]

Very helpful! Thank you! I'll look into this option.

[#7]

@mtarler I'm still working with your FILTER idea. Tried pivot table too. I keep coming back to how do I

setup the FILTER to be aware of the 10 names that do not change but may appear in today's daily report?

Here's a very small subset of my data. Names were changed. I'm trying to copy the rows that match certain names in column E to another spreadsheet (DailyRptB.xlsx). I'm really only concerned with matching on names, not numbers. For simplicity sake say Terri cooper, Barbara, Connie, JC Frank names are the rows I want to MATCH and FILTER on. Whatever FILTER criteria I use, tomorrow the column values may not have Terri, Barbra etc. but have Yuri, Tiffany, Marc (who also comprise my unique group of Sales people). 

SO my Filter should incorporate those names too in case they show up during the week.

Will my Filter function look like this?

=FILTER(A:A,ISNUMBER(MATCH($E:$E="Terri Cooper","Barbara","Connie","JC Frank","Yuri","Tiffany","Marc"$B:$G)))

Then how does the copy to another spreadsheet work?

[#8]

@jazmarc I have typed your example table in and created and example in the attached.

[#9]

Hi I happened to see this excel forum and I am trying to auto update the main data column that has a date column for contract.

How auto update into a new worksheet if the contract has dates, I need the entire row of information to be duplicate same row of info into another sheet

I cannot understand the above.. - {=IFERROR(INDEX($A$12:$D$21,SMALL(IF(COUNTIF($G$10,$C$12:$C$21),ROW($A$12:$D$21)-MIN(ROW($A$12:$D$21))+1),ROW(A1)),COLUMN(A1)),"")}

what the small ???

Please kindly help. Is there any video to see..

Thank you

[#10]

The formula you copied is an array formula used in the older Excel version before Dynamic Arrays was introduced. If you have Excel 365 you should consider the newer formulas like the FILTER formula I used above. If you have an older Excel version than you may need this type of formula. To answer you specific question the SMALL will simply return the X smallest value from an array. In this equation it uses ROW(A1) as the X and since A1 will increment for each cell it fills down to that is a way for the 1st cell in the column to return the 1st smallest value then the 2nd row returns the 2nd smallest value, etc... The array it is picking from is IF it matches then return the relative ROW where that match is located. And then finally the INDEX around the outside of it will take that relative ROW location and use it to look up the actual desired value. Hope that helps.

[#11]

hi @mtarler

I see thanks.. Will explore that. Thanks