StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFind value intersections in multiple columns
text
Body
copied!<p>I am well out of my depth here: Can this be done? And if so, what methods should I consider? </p> <p>I periodically receive a spreadsheet that contains a variable number of sheets. Each sheet has the same header row, but different values in the rows beneath. In one column is an identifying number that indicates a unique user, and I need to determine if there is an intersection between any of the Identifier columns on those worksheets. Here is a simplified example, in which the first and third worksheet have an intersection of abc789 but there is no intersecting value in Worksheet 2. I want to know when there is an intersection, and between which worksheets:</p> <pre> Worksheet 1: ID_Number • abc123 • abc456 • abc789 Worksheet 2: ID_Number • abc234 • abc345 • abc912 Worksheet 3: ID_Number • abc789 • abc567 • abc678</pre> <p>If it can be done, I'm suspicious of another problem: doing it in a way that works for 3 sheets today and 10 sheets tomorrow! To answer that question I tried setting variables for an unknown number of columns to compare like this, but clearly failed:</p> <pre><code>Dim iArraySize As Integer Dim iTabCounter As Integer Dim iLoopCounter As Integer iTabCounter = ActiveWorkbook.Sheets.Count For iLoopCounter = 3 To iTabCounter iArraySize = ActiveWorkbook.Sheets(iLoopCounter).Range("C2", Range("C2").End(xlDown)).Count dim aID & iloopcounter as Variant 'this line fails on compile with "expected end of statement" highlighting the ampersand aID1 = Range("C2", Range("C2").End(xlDown)).Value Next iLoopCounter </code></pre> <p>Is this a lost cause? Should I resolve myself to manual examination?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload