Listing values in between min and max

**sknu** · 01-22-2012, 05:05 AM

I have a large spreadsheet with minimum and maximum values for different entries, I need to create a list of the values (whole integers) in between the minimum and the maximum.
So for A, B and C I have a list of minimum values and maximum values (I have used semi-colon as cell-seperator).

A-min; 4; 0; 2; 3; 0; 1; 9;
A-max; 5; 1; 3; 5; 4; 3; 10;
B-min; 2; 4; 6; 1; 0; 3; 3;
B-max; 3; 5; 8; 3; 2; 5; 4;
C-min; 2; 4; 0; 1; 1; 3; 0;
C-max; 3; 6; 2; 2; 1; 4; 0;

I need to get this returned:

A; (4-5); (0-1); (2-3); (3-4-5); (0-1-2-3-4); (1-2-3); (9-10);
B; (2-3); (4-5); (6-7-8); (1-2-3); (0-1-2); (3-4-5); (3-4);
C; (2-3); (4-5-6); (0-1-2); (1-2); (1); (3-4); (0);

As I need to do this across a very large spreadsheet (100 rows and 300 columns) I would prefer a formula approach. Also I am not skilled in VBA, but will give it a go if that is the way around it.

Thanks in advance for your help

**martindwilson** · 01-22-2012, 05:30 AM

whatis the max value for max? is it 10?

**martindwilson** · 01-22-2012, 06:49 AM

with formulas id try it like this assuming range is min 0 max 10. (if its not and the upper and lower limits are further apart definitely go for vba as the table quickly gets very large)
but with your data set being quite large it may take time to calculate so vba may be a better method anyway

**sknu** · 01-23-2012, 04:27 AM

Yep. Overall maximum-value is 10, the overall minimum value is 0. All values can maximum range from 0-10. The ouput-format does not have to strictly fit the format I have set up here.
Actually it would be even better if I could get this returned:

A; (45); (01); (23); (345); (01234); (123); (910);
B; (23); (45); (678); (123); (012); (345); (34);
C; (23); (456); (012); (12); (1); (34); (0);

However, I know this does not make it much easier, as a find and replace on "-" would help me get this format.

Alternatively, I wouldn't mind doing it by concatenating a number of rows in a column after an initial treatment to get the series. But I can not think of a way to get a series with increments of 1 to dropdown in a column where every series has different starting points and different finishing points (different min and max values).