Complex comparison of columns (help!)

[phil.aubert]

Hi guys, i'll try to explain this as precisely as i can, but don't hesitate if there's any clarification required.

My high level goal: i have 2 reports (tab1 and tab2) on the same worksheet. Each report is concerning the same incidents (or ticket infos), but from two different systems. I would like to create a third report that would show incidents found on both tab1 and tab2, and include certain information from other columns to all respective incidents found.

What i am looking for: One column from both these tabs have more or less the same data (tab1, columnA contains precise incident numbers (ex: 554869). Tab2, ColumnJ contains, sometime, the same data as tab1, columnA, but sometimes it is junk like (n/a, or a bunch of numbers that correspond to tab1, columnA BUT with additional zeros before the correct number, so it is NOT an exact match, read 000000554869).

I'd like to look for the incident numbers (individually) from tab1, columnA, compare to see if the Tab2, ColumnJ contains these numbers in any cell, then if positive, write the numbers from tab1, columnA into a new tab3, columnA AND also bring over certain other columns found in tab1 and tab2, that correspond to that positive incident match (if a particular incident is found to be on both tab1 and tab2, tab3 would contain the incident number in question, plus let's say the title of the incident and the status of that incident, which are found on different tab respectively on tab1, columnB and tab2, columnC.

In short, IF numbers from tab1, columnA are found in tab2, columnJ (not an exact compare, but if it contains the numbers), then copy value of tab1, columnA into tab3, columnA, AND bring some respective values found on the same row of that particular incident number (i.e. tab1, columnB, cell C and tab2, columnC, cell E) .

I thank you in advance for at least taking the time to read and trying to help me on this one.

- Phil

[Jacc]

Post a sample workbook where you show the results you want to achieve, it will make it much easier to help you. If you have sensitive data then post a mock-up workbook.

[phil.aubert]

Post a sample workbook where you show the results you want to achieve, it will make it much easier to help you. If you have sensitive data then post a mock-up workbook.

Thanks for the feedback, i have attached my example file. On the 3rd tab, for each similar incidents found, i wanted to include the row infos of the blue columns (from tab1) and the gray ones (from tab2). You can see an example on row 10 of tab3, which shows what would appear with the proper formula. Rows in yellow from tab1 and tab2 are the data used for this example.

Example of the 3 reports.xlsx

[Jacc]

Something like this then? Note that there are three hidden helper columns to the left. Also note that column D contains an array formula, it has to be entered with Ctrl + Shift + Enter.

[phil.aubert]

Thanks Jacc, this is exactly what i was looking for. i'll take a look at how you did all this to understand correctly.

This really helps a lot !

[phil.aubert]

Hi Jacc, i got a few questions after reviewing the spreadsheet.

In tab3, hidden column A, what does the 0 value means? Also, if you could explain the logic and what are the numbers in that column, that'd be appreciated.

In tab3, after record #60, why all other results have no value ?

Thanks again for your much appreciated help!

-Phil

[Jacc]

Glad to hear it works!
On row 7: =MIN(IF(ISNUMBER(SEARCH(Table1[@[Incident Number]];Table2[SAP CSS or OSS '#]));Table1[@[Incident Number]]))
The @ means that it takes the number on the same row (7) but in Table1 the column named Incident Number. It then searches for that number in Table2, entire column SAP CSS or OSS '#. If it finds the number there it will return that same number. If it does not find the number there it will return FALSE. Since it is an array formula it will return an array of FALSE, one for each row where it could not be found. The min function is used to extract the number if it was found but applying MIN to an array of FALSE will return 0. In other words, 0 means that the number on the corresponding row in Table1 could not be found in Table2.

Since the numbers that could not be found is not interesting I am using column D to compress column A. In other words, column D contains the exact same numbers as column A (but not in the same order) except for the zeroes. This also explains why there are no values below row 60, only 60 incident numbers from Table1 was found in Table2. I used the IFERROR statement to convert the error to a "-" since that looks better, you can do the same on the rest.

[phil.aubert]

Thanks for the explanations, appreciate it !

Since the number of data on table 1 and table 2 will change often, can i use the same report template you provided, simply paste the new data (keeping same column names on row 1 for both tables), and check the result on the table 3 ?

I asked because i tried a real report case (by pasting updated, real data on table 1 and 2), did manual validations and it seems to miss 4 entries (their incident numbers on table 1 are found in table 2, but they are not pasted as results on Table 3).

I added a test file so you can see what i mean (Example of missing data on Table 3.xlsx).

I noticed that there are only 88 rows on Table 3, but there are 93 rows on Table 1. Does this mean i should modify the formula for Table 3, column D, so that it does a validation for lets say 150 lines ? i.e. =IFERROR(SMALL(IF($A$2:$A$150<>0,$A$2:$A$150),ROWS($D$2:D2)),"-"). Also, would i need to do any other modifications ?

P.S In the spreadsheet example, data in Yellow across all tables are the same match, the 4 in red (on table 1) are the ones that should have been found and written on Table 3.

[Jacc]

Just select the last row with formulas on sheet 3 and pull/copy down as long as you want, that should solve your problem.
Unfortunately there is no way of making the formulas do this automatically.

[phil.aubert]

Alright, i will try this out, thanks !

One last inquiry on this topic, if i were to create a table 4, to display the incidents from Table 1 that were NOT found on Table 2, could you please give me an example of what i could use?

Again, really appreciate your help!

[Jacc]

Something like this maybe? I tableified the other table and did the proper errorhandling dashes. What I wrote before about just copying down was not true since column D contained a range in the formula. Now that it is a Table it should work. Formulas look a bit bulky thoug but that's the way it is. Make sure you test it thoroughly cause I'm not good at that.

[Jacc]

Actually, skip 4 and go straight to 5. This version has fewer array formula, better errorhandling and is more complete.

[phil.aubert]

Thanks a lot it is working very well, manually checked many cases and it is very helpful. The challenge will be for me to familiarize myself with the formulas and how to do this, since i want to be able to create others in the future.

Take care Jacc !

[phil.aubert]

Thanks a lot it is working very well, manually checked many cases and it is very helpful. The challenge will be for me to familiarize myself with the formulas and how to do this, since i want to be able to create others in the future.

Take care Jacc !