Searching and comparing data from multiple sources with non-contiguous "headers"

[escribblings]

Firstly, I will apologise for the lack of any "working" example. I wasn't sure how to present it.

Also, I'm hoping that this can be completed using worksheet formulas, CSE formulas if necessary - but avoiding VBA if possible, because Ihave no clue about VBA - however, if VBA is the only viable option, it will have to do.

To start - my source data is exported from 3rd party software, I need to do as little with the source as possible to make this non-technical-end-user friendly.

Problem #1 - locate a value based on its position in a matrix and then return this value to a master matrix.

In my poor example, there are 3 source matrix's (ABC, ABD & ACD) and 1 master matrix (ABCD)

Assuming I wanted to find the value that intersects A & B I want to be able to do an INDEX MATCH MATCH on the source data.

The source uses the same values for both the X & Y coordinates in the matrix. In my export data, the staring cell for the "headers" is always B2. The headers for each row/column appear at the top of the column and end of the row, forming a diagonal. The total number of headers varies from sheet to sheet, but always follow the pattern B2,C3,D4,...,X24,Y25,Z26

The problem is MATCH will only work with a contiguous range. I *can* put in a "helper" row/column, but want to avoid this if possible as I'll have to add it to all the source files manually, as will anyone who comes after me. So if there's anyway to convert the non-contiguous array of cells to a virtual range, or trick Excel into thinking that, withing a worksheet formula I'm all ears.

Otherwise I have this working with "helper" rows/columns.



The second part of the problem is checking multiple sheets and returning every match. Ideally I will be dropping the sheets as complete workbooks into a folder, and I'm hoping to be able to search all files within that folder. I know I can do this by explicitly referencing the file and sheet names, but I would like the formula to be dynamic and just search everything in the target folder. Worst case scenario, the sheets can be imported into the master workbook, in which case it just needs to search all the sheets barring the master sheet.

Every result should be returned into the same cell, with a refernce to which sheet it came from. So in theory the INDEX MATCH MATCH should return the contents of a specific cell within the sheet that identifies the sheet AND data at the intersection in the format ID, VALUE;

Obviously I am going to have to CONCATINATE the results to get them in the same cell.

To prevent returning results from sheets that have only 1 or 0 matches to the MATCH criteia, I presume I am going to need some form of conditional statement like IF, SUMIF, or COUNTIF - not that I'm overly sure how to implement the latter.




Lastly - as if I'm not asking too much from Excel and you guys already - I would like duplicate results to only be listed once, but with all the sheet IDs against them

Going back to my "Poor Example" - we have the following values

ABC
A,B = 10
A,C = 20
B,C = 10

ABD
A,B = 10
A,D = 25
B,D = 15

ACD
A,C = 15
A,D = 30
C,D = 15

This should return the following in ABCD

A,B = ABC, ABD 10;
A,C = ABC 20; ACD 15;
A,D = ABD 25; ACD 30;
B,C = ABC 10;
B,D = ABD 15;
C,D = ACD 15;