How to split values into new rows.

[daverix]

I have a list of SKUs and a list of year models that the part fits. How can I get each SKU to put just one year model in each row while keeping the same SKU?

[Sam Capricci]

I don't understand what this means

How can I get each SKU to put just one year model in each row while keeping the same SKU?

you have two distinct SKUs and three models for one and two for the other and a bunch of years in columns C through G.
since it is a small sample, a results tab with hand entered results would be very helpful.

[daverix]

It's quite hard to explain what I am trying to do. As you have advised I have added a result tab in book 2 to show what I am trying to achieve.

[FlameRetired]

Please find a helper column in Sheet1 column G2. The formula does a cumulative count of years in each row.

Please Login or Register  to view this content.

Then in columns F:H of 'Results' filled down until you get blanks

F2

Please Login or Register  to view this content.

G2

Please Login or Register  to view this content.

H2

Please Login or Register  to view this content.

[bebo021999]

Here's my thought

[daverix]

Nice. And it's easy to change to account for more columns. Thanks a lot.

[daverix]

Hmm. Having a slight issue with this. I have added more data and copied down the formulas but the results I am getting are trunkated. Please see example.

[FlameRetired]

bebo01999 is not online at the moment.

Try changing the range references in his formula to match your new data. The last row is now 25 instead of 6.

[daverix]

Cool thanks. as you can probably tell, I have only a basic understanding of Excel.

[FlameRetired]

You're welcome.

[daverix]

Thanks for all the help so far. Let's change it up a bit. What if instead of a list of years it was "from - to" instead but to get the same result?

[bebo021999]

So try again:
A2:

Please Login or Register  to view this content.

B2:

Please Login or Register  to view this content.

Both ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
C2:

Please Login or Register  to view this content.

[FlameRetired]

Another way. Does not require Ctrl + Shift + Enter.

Change the years ranges in 'Sheet1' C2:D6 any way you like. I used what you last posted including the blank cell D4.

In cell D1 a named helper cell that saves having to recalculate Total number of years in each cell. It's named TotalYears. I borrowed heavily from forum member BMV for this formula. In cell A2 filled down and across column B until you get blanks.

Please Login or Register  to view this content.

In C2 filled down until you get blanks will return the years.

Please Login or Register  to view this content.