Help with a difficult request

[RaptorZ]

I have a file that has multiple fields, there are 7 fields on this file (randomly populated) that are to be used to match to another file that contains all 7 fields (fully populated) once matched I bring back the data from other fields in the 2nd file.   So for example see attached.

any thoughts help would be greatly appreciated

[RaptorZ]

I believe it's called an index match function in excel

[Yoshiro_Fujimori]

I don't know what is "an index match in excel", but if you want to match all 8 records, you would need to define which keys are potentially used to join.

Assuming any record should match either 

[City] + [state] 

or

[zip],

you may want to join twice and union the output as below.

Output

City	state	zip	dogs	cats	burger	pizza	CODE
Dallas		71253		v	n	n	1
Chicago	IL	60001	y		y	y	46
	MA	20023	n	r	y	y	125
New York		51506	y		y	y	406
		50608	y				946
Boston	MA	20203	y		n	y	356
Denver	CO		n			n	64
Atlanta	GA		n		y	n	98

I hope this helps.

[RaptorZ]

Thank you for your response, I am certain I am not doing a great job explaining this, but perhaps this is a better way to explain:

all 7 fields are primary keys, and what has been proposed was to provide me at least 2 of the 7 (sometimes it could be more) and it may be in the example above just Pizza and Burger that is filled in, so there's potential to bring back multiple "CODE" values for that line.   example being Burger and Pizza = y  so I'd bring back for that line 46 , 125, 406 as the appended values

my real life ask is a file of 1000s of records with different combinations of 7 fields, to match to a file that has all 7 fields fully filled in, to get another field value to append to the partially filled in line (which again may have multiple values returned b/c they're rarely ever providing all 7 fields)

[Yoshiro_Fujimori · Accepted solution]

@RaptorZ ,

So if the field is empty, that means a wildcard.

You would need to evaluate each row in "Fully filled table" if it matches with each row in "Partially filled table".

You can do it with batch macro or Append Field tool.

The Append Field tool may cause Error if the Partially filled table has more than 16 records.

(You can change the tool configuration to allow it.)

Here is a sample workflow. I hope it helps.

Apparently the repetition of Filter Tool is not scalable in design.

But it works enough if there are only 7 key fields to compare.

If you want to make it more scalable, please try it when you have time.

Input Data

Partially filled table (last two rows are added for test of multiple match)

city	state	zip	dogs	cats	burger	pizza
Chicago	IL	60001	y		y	y
New York		51506	y		y	y
	MA	20023	n	r	y	y
Boston	MA	20203	y		n	y
Atlanta	GA		n		y	n
Denver	CO		n			n
		50608	y
Dallas		71253		v	n	n
					y	y
	MA

Fully filled table (same as original)

city	state	zip	dogs	cats	burger	pizza	code
deacon	MA	20023	n	r	y	y	125
Boston	MA	20203	y	V	n	y	356
Sacramento	CA	50608	y	X	Y	N	946
New York	NY	51506	y	Y	y	y	406
Chicago	IL	60001	y	Y	y	y	46
Dallas		71253	y	v	n	n	1
Atlanta	GA	35065	n	Y	y	n	98
Denver	CO	75606	n	Y	y	n	64

Workflow

Output Table

RecordID	city	state	zip	dogs	cats	burger	pizza	codes
1	Chicago	IL	60001	y		y	y	46
2	New York		51506	y		y	y	406
3		MA	20023	n	r	y	y	125
4	Boston	MA	20203	y		n	y	356
5	Atlanta	GA		n		y	n	98
6	Denver	CO		n			n	64
7			50608	y				946
8	Dallas		71253		v	n	n	1
9						y	y	46,125,406
10		MA						125,356

[RaptorZ]

Brilliant, I will try this when I return to work and let you know, but i believe this is the solution!  and barring no errors or issues I will mark as accept as solution tonight   Thank you sir!!!