+ Reply to Thread
Results 1 to 12 of 12

Formula to list out a list of values with multiple quantites

  1. 01-23-2023, 01:33 PM #1
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Formula to list out a list of values with multiple quantites

    I am trying to find a formula that can list out Values vertically (in a column) which are typed in with a single number for the amount, and a separate number for the quantity (see example file & image below). In the example file, Column G - where it says Complete List - is where I would like the list of values to populate. I would like it to do it automatically, and be able to list out up to 35 different Amounts and Quantities, and for the Complete List to populate the list automatically.

    I have shown the desired result on the right side. Is there a simple way to do this that will not bog down the file (sometimes arrays can cause my files to run slowly). I am just looking for the simplest way to accomplish this, even if it isn't simple

    Thanks

    EXAMPLE A.PNG
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-23-2023, 01:50 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Formula to list out a list of values with multiple quantites

    Hi Nitro,

    If you had the newest version of Excel then a VStack(Sequence()) answer might work for you. I'm sure one of the smart Gurus could make this a repeating Lambda function. My long answer looks like:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    VStack Sequence Long Way 365.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 01-23-2023, 02:32 PM #3
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Re: Formula to list out a list of values with multiple quantites

    Quote Originally Posted by MarvinP View Post
    Hi Nitro,

    If you had the newest version of Excel then a VStack(Sequence()) answer might work for you. I'm sure one of the smart Gurus could make this a repeating Lambda function. My long answer looks like:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Attachment 814580
    Hey Marvin,

    This works at it stands in the example, but would I need to list every single pair of cells (or in other words, each individual row) one by one? This could be a long list in some cases, so that's something to keep in mind.

    Also there's a problem... there won't always be the exact same number of amounts/quantities. If I delete any of them in the example, I just get "#Calc!" error. So I don't think this will be a very workable option.

    I am unfamiliar with a repeating Lambda function. Sounds interesting.
    Last edited by nitro22888; 01-23-2023 at 02:35 PM.
    Reply With QuoteRegister To Reply
  4. 01-23-2023, 02:52 PM #4
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Formula to list out a list of values with multiple quantites

    It sounds as though you have 365 & your profile is out of date.
    If that's the case try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-23-2023, 04:29 PM #5
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Re: Formula to list out a list of values with multiple quantites

    Quote Originally Posted by Fluff13 View Post
    It sounds as though you have 365 & your profile is out of date.
    If that's the case try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Thank you for sharing that!

    I also found a video just now on youtube which seems to work, using Vlookup and a Helper column.
    Reply With QuoteRegister To Reply
  6. 01-23-2023, 04:40 PM #6
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Formula to list out a list of values with multiple quantites

    Are you still using 2010 or do you now have 365?
    Reply With QuoteRegister To Reply
  7. 01-23-2023, 06:26 PM #7
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Taiwan
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,382

    Re: Formula to list out a list of values with multiple quantites

    Cell I5 formula , Drag down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-24-2023, 06:47 PM #8
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Re: Formula to list out a list of values with multiple quantites

    On my laptop at home I have 2010, but at work I have 365. So I usually try to use formulas that can be used on both, if possible.
    Reply With QuoteRegister To Reply
  9. 01-24-2023, 06:48 PM #9
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Re: Formula to list out a list of values with multiple quantites

    Quote Originally Posted by wk9128 View Post
    Cell I5 formula , Drag down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Thanks!! Perfect
    Reply With QuoteRegister To Reply
  10. 01-25-2023, 09:15 PM #10
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Taiwan
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,382

    Re: Formula to list out a list of values with multiple quantites

    @nitro22888 You're Welcome. Glad to help . Thank You for the feedback.

    If you finally get a solution please mark your thread as SOLVED:
    - Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
    - Please click on the *Add Reputation button at the bottom left. Whoever helps you, give it to whoever you like
    Reply With QuoteRegister To Reply
  11. 01-25-2023, 09:26 PM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Formula to list out a list of values with multiple quantites

    Here's a non-arry version, just needs ENTER

    =IFERROR(INDEX($D$5:$D$12,MATCH(0,INDEX(--(COUNTIF($I$4:I4,$D$5:$D$12)=$E$5:$E$12),0),0)),"")

    ignore/delete any {} that Excel may have inserted.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  12. 01-30-2023, 07:01 PM #12
    nitro22888
    nitro22888 is offline
    Registered User
    Join Date
    06-20-2016
    Location
    Houston
    MS-Off Ver
    2010
    Posts
    73

    Re: Formula to list out a list of values with multiple quantites

    Thanks Glenn!!!
    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. Taking a horizontal list with multiple values and converting it into a vertical list
    By mcfurlong1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2020, 10:45 AM
  2. [SOLVED] Comparing Multiple List Values and Creating Multiple Outputs in a List
    By Shankster in forum Excel General
    Replies: 10
    Last Post: 06-01-2018, 07:03 PM
  3. Need help with formula to list unique values from a list based on a condition
    By mischge in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-04-2016, 02:18 PM
  4. Data Validation List - Formula to Sort and Return Unique Values in List
    By beewketu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2015, 04:18 PM
  5. Formula to extract a list from an existing list ignoring 0 values
    By weso2k in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-16-2014, 10:18 AM
  6. Lookup up values in multiple possible columns based on a list of rows and a list of column
    By radwansk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2013, 07:01 PM
  7. Convert Data and quantites values into multiple rows
    By Boogsie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2013, 08:24 AM

Tags for this Thread

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