Share via

Excel - Find duplicates, then count Qty, then show split

Anonymous
2022-07-01T01:49:54+00:00

Hi,

I have 100's of lines in an Excel spreadsheet as below:

Ref Total Qty
160458 2
160458 2
160458 1
160458 3
150712 1
150712 2

What I'm trying to achieve is the below result, the 3rd column to:

  1. Count the 'total qty' for a Ref with the same amount (e.g. Ref 160458 = 8)
  2. Divide #1 with how many lines of that particular Ref there is (e.g. 8/4 = 2) & then enter this in the 3rd column.

Is this achievable?

Ref Total Qty SPLIT / Ref
160458 2 2
160458 2 2
160458 1 2
160458 3 2
150712 1 1.5
150712 2 1.5
Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-01T02:54:09+00:00

    Hi ,

    I'm Mukesh and I will be happy to help you out with your question.

    In Excel, you can use COUNTIF function to count the duplicates.

    Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need.

    You can follow the steps in the below article for detailed description of the steps that you need to follow . Please note that the below article is an non Microsoft website .

    https://www.extendoffice.com/documents/excel/1499-count-duplicate-values-in-column.html

    I hope this information helps. If you have any questions please let me know and I will be glad to help you out.

    --If the reply is helpful, please Upvote and Accept it as an answer--

    Best regards
    Mukesh

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2022-07-01T10:02:10+00:00

    Hi Angelo

    You may try the formula

    =SUMIFS(B:B,A:A,A2)/COUNTIF(A:A,A2) and adapt it to your real scenario

    I hope this helps you and gives a solution to your problem

    Do let me know if you need more help

    Regards

    Jeovany

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2022-07-01T03:31:53+00:00

    Re: "Is this achievable?"

    See the image below.

    But you need a method to list the unique "Refs"

    There is a "Uniques" function in newer versions of Excel.

    I used my own free "Professional_Compare" workbook that includes

    a 'List Uniques" utility.

    Fomula in D3 and filled down...

    =SUMIF($A$2:$A$7,"="&C3,$B$2:$B$7)/COUNTIF($A$2:$A$7,"="&C3)

    '---

    Nothing Left to Lose

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    (free excel programs)

    0 comments