Is it like the attached?

Attached Files

Raw DATA_2.xlsx (324.3 KB, 10 views)

Thank you, I have applied it to the sheet and have changed the cell it is working off however I do not think it is fully working please see attached.

There are cells returning a Zero instead of 1 or 2 etc

also as this formula essentially creates a new units column is it possible to have it return the sum as well as the individual numbers of the other cells or a zero if it has been summed? please see below.

i.e.

12345 - units 2 - count 0
12345 - units 5 - count 7
58244 - units 1 - count 1
48125 - units 9 - count 0
48125 - units 9 - count 18

Attached Files

RAW DATA.xlsx (397.4 KB, 13 views)

You wrote

I do not think it is fully working please see attached

But what is not working? Please be specific. I don't know what you mean by 12345, 58244 and 48125. None of these seems to be neither Product number nor Reference number.

I guess it is all completely obvious to you, but not to me.

It must be possible for you manually enter more (at least five) examples of expected results and specifically indicate which cells return wrong result.

Also I don't know what you mean by your last sentence.
(English is not my mother tongue which partly may explain my difficulties in understanding).

Sorry let me start again.

1. in the attached spreadsheet above you will see the formula does not seem to be adding up the units correctly, in some cells it is showing a zero instead of the sum of units.

2. secondly the codes I wrote were just examples of how I am trying to get it to work, if there is no duplicate unit counts than I need the formula to return the number of units shown.

3. However if there is a duplicate than I need the formula to return the sum of the units as it is doing now. please see below as an example of how I need it to work.

------------------sheet---Formula
------------------units----return
18175---SB-520384--1------ 0
18175---SB-520384--2------ 3
21222---SB-520384--1------ 1
40897---SB-520399--2------ 0
40897---SB-520399--1-------3
18173---SB-520384--1------ 1
18175---SB-520384--1------ 1

I hope I have made this all make sense.

Hi

Create an ODBC query with DISTINCT clause, which reads unique data from table with double rows and writes result to another sheet. You also can use SUM, AVEREGE, MIN, etc in query to get some aggregate values. Or you can use formulas like SUMIFS or COUNTIFS in adjacent columns to query table to calculate aggregate values from original data table.

But I tried to do as you showed in the file uploaded in #3.

Is it better now?

Attached Files

RAW DATA_3).xlsx (457.5 KB, 10 views)

I added edited RawData.xlsm file, where ODBC query is used to consolidate your data.

The format *.xlsm is used, as I added an Open event which corrects query parameters on open automatically, whenever the file name or location is changed. Without this this script allowed to run, you have to edit the query datasource manually every time you relocate or rename the file.

In your original table, I removed a couple of spaces from column names.

As source table for query I defined a non-dynamic named range nData. Currently it has 20000 rows - when your table has more rows, then edit nData.

Along with sum of Unit's I calculated also the number of deliveries and min of FromDate and max of ToDate.

Attached Files

RawData.zip (507.9 KB, 8 views)

Wow thank you this all looks very impressive, I am not to sure how to read it though.

I have just looked at the Raw data sheet and have had a play and it looks like i can work with this and use it to complete everything i need. thank you so much for this.