+ Reply to Thread
Results 1 to 11 of 11

Create list, listing each item multiple times based number in another column.

  1. 01-28-2014, 04:43 PM #1
    rhihenderson
    rhihenderson is offline
    Registered User
    Join Date
    03-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Create list, listing each item multiple times based number in another column.

    I have a table that is exported out of a software. It shows a list of item numbers in one column and the count of those items in another column. I need to create a list of all the item numbers listing each number a seperate time based on the count. So item number 031031 has a count of 3 and item 021563 has 5. I need to create a list with 031031 listed 3 times, and 021563 listed 5 times.

    I have done this before but for the life of me can not remember how or find my notes on it.

    Thanks,
    Rhi
    Reply With QuoteRegister To Reply
  2. 01-28-2014, 05:01 PM #2
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Create list, listing each item multiple times based number in another column.

    Assuming data in A1, count in B1, then C1:

    =IF(COLUMN(A1)<=$B1,$A1,"")

    and copy over and down as much as needed

    list numbers.xlsx
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!
    Reply With QuoteRegister To Reply
  3. 01-30-2014, 01:15 PM #3
    rhihenderson
    rhihenderson is offline
    Registered User
    Join Date
    03-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create list, listing each item multiple times based number in another column.

    Thank you, but I need all the results to list in one column so I can link it into another software to create labels...
    Reply With QuoteRegister To Reply
  4. 01-30-2014, 01:48 PM #4
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Create list, listing each item multiple times based number in another column.

    Oh. Assuming you don't need a delimiter, you could just use =TRIM(REPT(a1&" ",b1))

    REPT means repeat. It repeats the first parameter a number of times described by the second parameter.

    TRIM just drops off the last space.
    Reply With QuoteRegister To Reply
  5. 01-30-2014, 03:03 PM #5
    rhihenderson
    rhihenderson is offline
    Registered User
    Join Date
    03-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create list, listing each item multiple times based number in another column.

    That's a great formula to know, but won't help me here.

    Let me give example of what I need:

    Column A | B
    031031 | 4
    021111 | 5
    025813 | 1

    New List would be
    031031
    031031
    031031
    031031
    021111
    021111
    021111
    021111
    021111
    025813

    Thanks.
    Reply With QuoteRegister To Reply
  6. 01-30-2014, 03:51 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Create list, listing each item multiple times based number in another column.

    I couldn't get the numbers one under the other but this might help you come to a solution.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  7. 01-30-2014, 04:02 PM #7
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Create list, listing each item multiple times based number in another column.

    I was able to teach myself enough VB to make this work:


    CopyXTimes.xlsm


    Please Login or Register  to view this content.
    In Excel hit Alt+F11, Insert -> Module, Paste the code here. You will need to change the sheet names and range to accommodate your needs.

    I did this on sheet2, with strings in A2:A5. Change that in the code whatever you need. Make sure Column C has a header on row 1.

    Then close that window, hit Alt+F8 and choose CopyXTimes and hit okay.
    Reply With QuoteRegister To Reply
  8. 01-30-2014, 04:17 PM #8
    PietBom
    PietBom is offline
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Create list, listing each item multiple times based number in another column.

    @rhi,
    Are you interested in a VBA solution ?
    see attachment........

    Funny: 2 people were doing the same on the same time (1 in Pennsylvania and 1 in the Netherlands)
    Attached Files Attached Files
    Last edited by PietBom; 01-30-2014 at 04:27 PM.
    Kind regards,
    Piet Bom
    Reply With QuoteRegister To Reply
  9. 01-30-2014, 04:34 PM #9
    rhihenderson
    rhihenderson is offline
    Registered User
    Join Date
    03-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create list, listing each item multiple times based number in another column.

    This looks like it will work. I will have to work on it tomorrow.

    Thank you so much for your help. I will marked solved tomorrow once I'm sure all is working as needed.
    Reply With QuoteRegister To Reply
  10. 01-30-2014, 04:37 PM #10
    rhihenderson
    rhihenderson is offline
    Registered User
    Join Date
    03-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create list, listing each item multiple times based number in another column.

    Piet Bom,

    I am not opossed to VBA, I'm just ignorant of it!
    Thanks!
    Reply With QuoteRegister To Reply
  11. 01-30-2014, 04:42 PM #11
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Create list, listing each item multiple times based number in another column.

    That makes two of us.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find number of times a value from a list appears in a seperate column
    By GSmith8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 02:05 AM
  2. How to create drop down list in Excel where each list item is based on two columns?
    By matkiros in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2012, 08:50 AM
  3. List how many times an item appears in a category in another column
    By caranpaima in forum Excel General
    Replies: 12
    Last Post: 07-21-2011, 09:29 PM
  4. Recreating a column but listing each item multiple times
    By katie1343 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-07-2007, 05:34 PM
  5. How to paste an item multiple times in a column
    By Rock in forum Excel General
    Replies: 5
    Last Post: 11-29-2005, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1