Auto Transfer Cell Contents Multiple Times to Another Column

Hey guys, I'm new here. Familiar with Excel but not a guru by any stretch. An image is attached showing what I want to do...and I'm sure it's doable but I'm not aware of the method to do it. It may require a VB script as well...

I want to be able to paste a string of part numbers into a column, it be recognized by the four letter acronym, and a specified number of that part then pasted (consecutively) into a column on another sheet. I don't have an example sheet yet as we're just planning at the moment and this is the "hard" part.

Thanks for any direction or ideas you may be able to give :-)

Image.jpg

See attached file, which uses formulae only.

Column B generates the number of times each entry needs to be duplicated, using this formula in B2:

=IF(COUNTIF(A2,"*PGGV*"),2,IF(COUNTIF(A2,"*BGGL*"),4,IF(COUNTIF(A2,"*PALG*"),1,0)))

Column C is just a cumulative count for those numbers (note the zero in C1), and column E gives you the result using this formula in E2:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1)-1,C:C)+1))

which can be copied down sufficiently far to display all the values.

Hope this helps.

Pete

It still uses column B as a helper.

By the way, your profile still shows XL2007, though you wouldn't be able to use the AGGREGATE function if you are still using that, so please update your profile.

Pete

Thanks for updating your profile.

I was given XL2019 last year as a Christmas present, but I still haven't installed it yet - I'm a dinosaur !!

Pete

You guys are fantastic. This appears to be what I'm looking for - I'll try it out later today and report back. Thanks for the help!

Glad to help.

If, after trying the proposed solutions out, you feel that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete