combining multiple columns of data to one column

[jcalvacca]

Hello all and thanks!

See attached for vision....
Have test scores from two different locations and would like to use the data for further analysis......

Possibly a macro but not sure if it is.......
Project has only two locations but many more charts in different locations on the sheet so an explanation of the formula and how it works would be helpful.

Thanks!

 

[eibi]

Take a look at the attached...

I'm producing the target output that you requested (with one exception, which I think is a typo in your sample), but I have some reservations about the structure of your input data...We may need to rethink these formulas after you apply them to the real data set.

Formulas appear complicated, but here's a summary:

1. Where there are non-blanks in your data set, the formula returns the Row and Column of the corresponding data element as a numerical value. For example, data in the 7th row and 2nd column of the data range is returned as (7.02). The result looks something like this (7.01, 7.02, 7.03, FALSE, FALSE, FALSE, 9.01, 9.02, 9.03...) -- where the "false" values represent blank cells.
2. Next, the formula sorts these addresses from small to large, and sorting out the FALSEs (7.01, 7.02, 7.03, 9.01, 9.02, 9.03...)
3. Then the formula extracts the row (7) and column (.02*100) for each data point, and applies that address using an index formula INDEX(Range,7,2) to lookup each sequential non-blank value in the range.

Look it over; see if it's doing what you want.

Thanks again to ninja @NARAYANK991 who introduced me to this method when I posted a similar question once upon a time...