excel function by using match and count

1. 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!

2. 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. Re: excel function by using match and count

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

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
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

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