+ Reply to Thread
Results 1 to 9 of 9

Find row Number of each value in list

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Davenport, Iowa
    MS-Off Ver
    Excel 365
    Posts
    193

    Find row Number of each value in list

    Hi,

    I have a list of orders on a spread sheet that are organized by date, then I have a list of All Orders, however, I need to fill each order from top to bottom and I was wondering if there is a way to have a VBA script to pull the row number that it is found on from the Orders sheet. Please keep in mind, the Orders sheet goes out to column ZZ, and I have about 10,000 orders which will need to be found..
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Find row Number of each value in list

    You can use a simple UDF:

    Please Login or Register  to view this content.
    Use as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Davenport, Iowa
    MS-Off Ver
    Excel 365
    Posts
    193

    Re: Find row Number of each value in list

    This works, however, for 10000 rows this takes a long time to calculate everything, is there any other option?

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,539

    Re: Find row Number of each value in list

    Don't know how long it will take with the looping.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Re: Find row Number of each value in list

    This seems to work pretty fast, I didn't however test with that much data:
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    714

    Re: Find row Number of each value in list

    try

    Please Login or Register  to view this content.
    Regards,
    MohanS


    "Perfection is not attainable, but if we chase perfection we can catch excellence." - Vince Lombardi

    You can simply say thanks by clicking "*Add Reputation" icon

  7. #7
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    714

    Re: Find row Number of each value in list

    ................

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    824

    Re: Find row Number of each value in list

    Try this:
    sheet2 must be the active sheet when you run the code


    Please Login or Register  to view this content.

  9. #9
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Find row Number of each value in list

    You could use Power Query to retrieve all orders and the first row on which they occur. This should be pretty fast:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] To find number from list
    By michael35 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2018, 02:30 AM
  2. VBA: Multiple text and number columns. Find list and number of unique words
    By first_jaguar in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-14-2017, 09:58 PM
  3. [SOLVED] How do I find the number of the list is the addition of an equal number?
    By wiliam_s in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-21-2015, 10:30 AM
  4. Trying to find number of times an ID number appears in a data list
    By RohanF in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-10-2015, 09:12 PM
  5. How To Find Number From List
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2010, 10:39 PM
  6. Find predominant number in list
    By littler in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2008, 10:22 PM
  7. [SOLVED] find next number in list
    By zero in forum Excel General
    Replies: 3
    Last Post: 09-27-2005, 06:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1