I have a workbook which contains 6 sheets (that number is dynamic it's only given as a base to get code going). I want to
1-loop through the last 5 sheets in their respective entire column B content (starting at B3) and select only the distinct value of the combined range of those 5 columns B (it's always going to be column B - static). That means some values may only be present in one sheet while some others may be on all sheets...I need to return only one in both cases.
2-I want also to sort the range ascending alphabetically and then paste the results in the first sheet, starting at A5 (this is also static).
Now onto the tricky part; the pasted results are actually a key that, as already said, may be present in one or multiple sheets. I want to use that key, once the range has been defined and pasted, to retrieve other data in those 5 last sheets, depending on which sheet(s) the key is.
For example, using A5 key, I want to retrieve in each of those 5 sheets, data in their columns C, D, E, F, G, H, N, O, I (in that respective order). I know that columns C, D, E, F, G, H are going to be returned only once because they are the same throughout the worksheets but N, O, I is causing me problems since the columns might be returned as many as 5 times in my example.
Look at the below example of the final results. It works just as if I was doing a vlookup based on A5 on each of the last 5 sheets, returning each columns individually. I don't want to use this approach because I want the generated data to be static.
| Sheet 1 | Sheet 2 | Sheet 3 | Sheet 4 | Sheet 5 |
+----+----+----+----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+
| Key| C | D | E | F | G | H | N | O | I | N | O | I | N | O | I | N | O | I | N | O | I |
+----+----+----+----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+----+
----+
Help ! Code needed. Thanks
Start Free Trial
