Create range of numbers on one row from incrementing numbers on multiple rows

[mexykanu]

Hello,

I would like to ask for help on the following issue:
The following table is given:

flower	20		rose
flower	21		rose
flower	22		rose
flower	31		blossom
flower	32		blossom
tree	1		apple
tree	2		apple
tree	3		apple

Which needs to be turned into:

flower	20	22	rose
flower	31	32	blossom
tree	1	3	apple

The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.

Could you please give me some help regarding this issue ?
I should have learned VBA until now but i've started learning PHP instead.

Thank you.

[Ron Coderre]

How about letting a pivot table do the work for you?

I put your data in cells B3:D11, assigning headings and skipping the blank column:

Please Login or Register  to view this content.

I converted that range into an Excel Table (Because they're so easy to work with)
• Select the data range
• Home.Format_as_table....follow the prompts

Then I created a pivot table, based on the Excel Table.
• Row Labels:
...Category
...Type
Values:
Seq
Seq <-yes, I used Seq twice

Set the 1st seq formula to MIN
Set the 2nd seq formula to MAX
...Use the Classic Display
...Remove subtotals and the grand total

This will be the result:

Please Login or Register  to view this content.

Is that something you can work with?

[mexykanu]

Hello Ron,

Thank you for the tutorial, this is a very good solution.
Could you please tell me: is there a quick way to display the "Category" instead of the blank cells ? I know this feature is included in Excel 2010 but i haven't found it in Excel 2007.

Thank you.

[Ron Coderre]

Unfortunately, no. (Unless "Copy/Paste_Special.Values...Edit the cells" is an acceptable option)
Excel 2007, IMHO, is an incomplete thought. Excel 2010 and Excel 2013 are definitely better.

[mexykanu]

Hello,

Alright, i'll look into Excel 2010. Thank you very much for your support.

Good night.