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?
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?
I don't understand what this means
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.How can I get each SKU to put just one year model in each row while keeping the same SKU?
since it is a small sample, a results tab with hand entered results would be very helpful.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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.
Please find a helper column in Sheet1 column G2. The formula does a cumulative count of years in each row.Formula:Please Login or Register to view this content.
Then in columns F:H of 'Results' filled down until you get blanks
F2G2Formula:Please Login or Register to view this content.H2Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Dave
Here's my thought
Last edited by davesexcel; 03-04-2020 at 07:58 AM.
Quang PT
Nice. And it's easy to change to account for more columns. Thanks a lot.
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.
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.
Cool thanks. as you can probably tell, I have only a basic understanding of Excel.
You're welcome.
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?
So try again:
A2:
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.Please Login or Register to view this content.
C2:
Please Login or Register to view this content.
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.
In C2 filled down until you get blanks will return the years.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks