Hello everyone, and thank you for reading this message 
I am currently working on a small project, where I need to find all the data corresponding to a customer in 6 sheets, each being a different year, when typing that customer ID in a specific cell, such as a google search.
I managed to look at that customer ID in 6 sheets, from 2015 to 2020 (Table 1). For this, I used a INDEX and SMALL IF based on the customer ID, changing the year for each column so the formula looks in the correct year sheet. I also gave each column 20 rows, as a customer will never appear more than 20 times each year.
However, depending on the customer researched, the amount of data will vary in each column, creating blanks if it appears less than 20 times. (Table 1)
Moreover, Table 1 is a working table, that I use to gather the data before presenting it in another table. (Table 2)
Now, I would like to create a table in the same sheet, where those columns would go nicely under each other, to create that illusion of historic. (Table 2)
My challenge is, as the amount of data varies depending on the customer and year, the Table 2 will show blanks when I try to add them under each other, using INDEX formula.
My objective is therefore to add those columns under each other, with a formula that would disregard "blanks" despite them being formulas returning no data, and with a number of rows adapting to the table data, and to any new customer ID's data.
If anyone has an idea of how to create such an adaptive table, you will become a true savior !
Thank you
Table 1:
Table 2:
I am currently working on a small project, where I need to find all the data corresponding to a customer in 6 sheets, each being a different year, when typing that customer ID in a specific cell, such as a google search.
I managed to look at that customer ID in 6 sheets, from 2015 to 2020 (Table 1). For this, I used a INDEX and SMALL IF based on the customer ID, changing the year for each column so the formula looks in the correct year sheet. I also gave each column 20 rows, as a customer will never appear more than 20 times each year.
However, depending on the customer researched, the amount of data will vary in each column, creating blanks if it appears less than 20 times. (Table 1)
Moreover, Table 1 is a working table, that I use to gather the data before presenting it in another table. (Table 2)
Now, I would like to create a table in the same sheet, where those columns would go nicely under each other, to create that illusion of historic. (Table 2)
My challenge is, as the amount of data varies depending on the customer and year, the Table 2 will show blanks when I try to add them under each other, using INDEX formula.
My objective is therefore to add those columns under each other, with a formula that would disregard "blanks" despite them being formulas returning no data, and with a number of rows adapting to the table data, and to any new customer ID's data.
If anyone has an idea of how to create such an adaptive table, you will become a true savior !
Thank you
Table 1:
2015 | 2016 | 2017 | 2018 | 2019 | 2020 |
| ID15a | ID16a | ID17a | ID19a | ID20a | |
| ID15b | ID16b | ID17b | ID20b | ||
| ... | ID17c | ... | |||
| ID15n | ID20n | ||||
Table 2:
ID |
| ID15a |
| ... |
| ID15n |
| ID16a |
| ID16b |
| ID17a |
| ... |
| ... |
| ID20n |
