Consolidating lists excel 2016
In Excel 2007, users cannot use reference to other worksheets or workbook for Data Validation criteria.
I am trying to create a formula that returns the concatenation of two arrays of different lengths.
I need this concatenation for part of another formula and I would like to avoid "helper" rows, if possible. The goal is to have the output be Excel cannot directly concatenate arrays in the way you describe (i.e.
simply combining them back to back.) However, there is a (complicated) solution to this problem without using helper functions. Once you have that result, you can add the two arrays of length 7 together to get the desired result.
INDEX($B:$B, ROWS(H2:$H)) becomes INDEX($B:$B, 1) becomes INDEX(, 1) and returns "AA" in cell H3.
Create a dynamic list and remove blanks into it, and then drag the autofill handle down to the range you need.
Now you will see only the cells with data has a number next to.
Again without explaining much of the "why" because I think the discussion will get too long and off-topic, here is a formula that generates the first matrix of 1's and 0's above: = MMULT( (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1: A4) ROWS(B1: B3),1))) =COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1, ROWS(A1: A4))))) 0, A1: A4) MMULT( (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1: A4) ROWS(B1: B3),1))) =(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1, ROWS(B1: B3)))) ROWS(A1: A4))) 0, B1: B3) FINAL NOTES/THOUGHTS The advantage to using this formula is that it allows arrays to be concatenated without using VBA.
The disadvantage is that this method for concatenating arrays only works with numbers, not text.