+ Reply to Thread
Results 1 to 4 of 4

excel function by using match and count

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    4

    excel function by using match and count

    Hello, I am a beginner with excel. I have a big list (vertical list) of product numbers. The numbers are not in a database and i want a function which counts all the different product numbers in that list and all the matches in the list with the same product number. Is this possible?

    example

    product number

    ABC-01
    ABC-02
    ABC-03
    ABC-01

    The outcome need to be

    3 different product numbers:
    ABC-01
    ABC-02
    ABC-03

    Matches:
    2x ABC-01
    1x ABC-02
    1x ABC-03

    Thanks a lot for helping me!
    Last edited by timo-osinga; 09-04-2012 at 04:33 AM.

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: excel function by using match and count

    Hi
    try this macro

    it will search the range InputRange, list unique values in a column begining at cell OutputCell and list the number of occurrences in the cell to the right of that. You will need to set the ranges according to your data

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: excel function by using match and count

    Another way using formulas.
    See the attached file with formulas in B,C,D columns.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-04-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: excel function by using match and count

    Hello, thank you for helping me last time! I still have some difficulties with macros. Can U help me please one more time?

    This is the current (example) situation

    Date FA Task Barcode Jobcard Part Number Qty Part Description
    24-01-2009 A06 T0003ABC book 123456 1 PAPER
    24-01-2009 A06 T0003JKL seat 456789 2 ARMREST
    25-01-2009 A05 T0003FJH seat 456789 30 ARMREST
    25-01-2009 A05 T0003VBN plastic 321654 1 TRANSPARANT


    Last time U created a macro that sorted all unique part numbers for me. Next to that column there was the ammount of the unique part number.

    Now i need to do the following (if possible):
    I want the following columns:

    1. Unique part numbers (did last time)
    2. Ammmount of unique partnumbers (did) last time
    3. The Qty of each unique partnumber
    4. The dates involved with each unique partnumber
    4. The FA involved with each unique partnumber
    5. the task barcode involved
    6. Part description

    so this need to be the result;

    Part Number Ammount QTY Dates FA's Barcodes Part description
    456789 2 32 24-01-2009, 25-01-2009 A05, A06 T0003JKL, T0003FJC ARMREST
    123456 1 1 24-01-2009 A05 T0003ABC PAPER
    321654 1 1 25-01-2009 A06 T0003VBN TRANSPARANT


    Thanks a lot! if U have the time to help me, otherwise i need to post this in a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1