Custom list sequence

Hello folks,

I'm not sure if there is a simple solution to what I'm trying to achieve or if it requires a script. Basically, I'm trying to see if I can fill multiple rows with sequential data from a custom list with other content in each cell.

EXAMPLE

Size 26" Shoes
Size 28" Shoes
Size 30" Shoes

I have created a custom list of numbers that increase by two. But I have to have the remaining content in there. Using the fill handle results in the same text repeated. The sequence only works when I have only numbers in cells. Is it possible to direct Excel to look for specific data within cells and work with that? or is this just not possible?

Thanks!

Hi,

try this if , Not satisfied provide a sample workbook with out any confidential data .

Punnam

Or this:

=CONCATENATE("Size"," ",2*(ROW()+10),CHAR(34)," ","Shoes")

Thank you so much to both of you!

It does definitely work for the example I gave you. Though I'm wondering if this is really the only way? Because, in my example, there are just two words, one on each side. But what if I have more words and the numbers are in a different place, i.e. in front instead of in the middle. I was wondering if there was also a method in which no matter what other text exist and how it was placed, Excel would only focus on the numbers and apply the formula to the numbers only.

Thanks again!

Thank you for the reply.

Absolutely correct. In that case, I will make it easy as possible for you guys to help me. This spreadsheet is simply going to be made of names of various products. I have a SKU column and a Title column. I have established a naming convention which puts the numbers that need to change in a specific place every time. But in the case of SKU column, the numbers' location may change due to longer string of characters that fall before it.

For clarification, please see the attached file. So in the SKU column, the important numbers lie at the end of each cell, i.e 70, 72, etc. Those same numbers can be found with quotes under the Title column, but there positioned in the beginning. The Title column should be fairly straightforward because the numbers always start at the beginning. But in the SKU column, the numbers fall at the end which complicates things because as you can see from the last SKU in the column, the SKUs can get longer or even shorter for that matter. So again, I'm simply trying to make it so that when I use the fill handle to copy cells going down, Excel should simply change those last two numbers by increment of two in both columns.

Hope that makes sense.

Thanks again!


example.xlsx

I apologize. I wasn't clear.

I need two separate formulas, one for each column. The SKUs and the titles are independent and their specific formatting should stay the same.

Hi,

Let me know details of row colored in yellow background .

Punnam

Punnam, thank you for the sample.

You know, I really think the solution you guys have provided me with thus far has been doing exactly what was intended. Basically, I was trying to avoid manually entering the sizes, i.e 70, 72.

I would insert for example, BLTR04140170, then drag down few rows copying that across those rows. Then I would go back and manually change the 70 to 72 to 74 etc etc. With the CONCATENATE formula and the one sktneer provided, I have to fill in the data once and simply drag down and it automatically changes the numbers by increment of two. It works and the only thing I have to do provide the initial text. Unless there is a way to make this process more efficient, I think I'm happy with this solution. Thank you all for weighing in. This definitely makes my work a lot easier.

hi cl0n,

I think I'm happy with this solution,
I think you are happy and got your solution ,if so mark the thread solved & rate one who helped you, if you are looking for further help provide the data as "FDibbins" stated in Post11.

Punnam