1

I have 2 excel file/ worksheet. One is internally maintained within the company and one is generated from SAP system. My task is to find out if the 2 files are matching or not. If not what is different, the one maintained in Input Data 2 is the correct one. It's a large data set and I don't know how to solve it? Input Data 1:

SAP Account Nr. Company Account Nr.
6020 55300000
8170 55790000
8180 55800000
8180 55850000
8156 56300000
8151 56400000
8165 56500000
8165 56500101
8100 56600000
8100 56650000
8100 56725000
8100 56750001

Input Data 2:

Company Account Nr. SAP Account Nr.
55750000 6020
55750000 6020
55760000 6020
55770000 6020
55790000 8170
55800000 8180
55850000 8180
56300000 8156
56400000 8151
56450000 8150
56500000 8165
56500101 8165
56556000 8007
56600000 8100
56650000 8100

I have attached the screenshot. Can someone please help?

Input Data 1

Input Data 2

David Leal
  • 6,373
  • 4
  • 29
  • 56
AshSam123
  • 21
  • 3
  • Please provide the input data in table markdown format, it is not clear to me based on the input data what you want to achieve. Probably you can put your problem in terms of sample data that doesn't require to understand your specific problem – David Leal Nov 12 '22 at 13:41
  • Hi David. Sorry my question wasn't clear. Let me put this way. I have 2 excel file/ worksheet. One is internally maintained within the company and one is generated from SAP system. My task is to find out if the 2 files are matching or not. If not what is different? Let me edit my original post and share the input data. – AshSam123 Nov 14 '22 at 06:48
  • Hi AshSam123, so both columns from Data1 need to match Data2 (correct dataset), and if not find the differences. Please put provide the input data in table markdown format, so it easier to copy the information to reproduce it. You can use this tool: [Table Generator](https://www.tablesgenerator.com/markdown_tables#), copy the data from your Excel to Table Generator, then generate the markdown and finally to your question. – David Leal Nov 14 '22 at 13:45
  • Hi. I've provided in the table markdown format. I hope this is how you wanted. I am new to this forum and I hope I've used the tool in correct way. Thanks – AshSam123 Nov 18 '22 at 07:13
  • Thanks, @AshSam123. Check my answer and let me know if that is what you are looking for. – David Leal Nov 18 '22 at 17:53

1 Answers1

0

You can try the following in cell H3:

=LET(refRng, E3:E17&F3:F17, rng, B3:B14&A3:A14, input, A3:B14,
 SORT(FILTER(input,ISERROR(XMATCH(rng, refRng)), "NO MISSING DATA")))

and here is the output sorted of missing data in Data 1: sample output file

LET function for easy reading and composition, without LET function you can use:

=SORT(FILTER(A3:B14,ISERROR(XMATCH(B3:B14&A3:A14,
 E3:E17&F3:F17)), "NO MISSING DATA"))

Basically, it searches for non-matches in rng from refRng. We concatenate both columns because we need to search for a match in both columns. XMATCH returns #N/A (error) in case of not found, and this is what we want to filter from input dataset via FILTER function. In case all rng elements are found. We use the third input argument of FILTER to return the message of non-missing data.

If you want the output with the same order or columns as in Data 1, then replace the first input argument of FILTER with:

HSTACK(INDEX(input,,2), INDEX(input,,1))

or

CHOOSE({2,1}, INDEX(input,,1), INDEX(input,,2))
David Leal
  • 6,373
  • 4
  • 29
  • 56