Marking first instance of unique value in large range, on low powered machine

[e4xit]

Hello, I have a large range of (sales) data which includes multiple orders ("order number"-s). Each order can contain 1 or multiple "lines". Each line can (confusingly) contain more than one entry. The reason for this is that a Line might contain values for: Manufacturing, Delivery, Handling Charge etc.

The result of this is that "Count"-ing order numbers produces incorrect number of orders per customer (in Pivot Table). The same with "Count" of Line Number. What I need to be able to do is to "Count Unique" values, something I am aware that might be possible in Excel 365 Pivot Tables, but not to me.

I have previously CONCATENATED some values to produce an "ID" which will be the same for each customer order (customer number + order number) and for each customer "line" (customer number + order number + line number). The calculation of these "ID"s was ok on my machine.

I then tried to use a formula:

=(COUNTIF($BA$2:$BA2,$BA2)=1)+0

...which will count the first instance of each ID in the array and return "1" in the adjacent column, of the row which that first instance occurs. Excel then has no difficulty summing these 1's to give me correct number of orders and lines for each customer.

My difficulty has come because when used on final data range it kills my machine. My test range of 100 rows was fine, my actual range of 22,000 rows is painful and I have not even attempted on 65,000 full range. I believe that this is a combination of having to perform

CONCATENATE

and

=(COUNTIF($BA$2:$BA2,$BA2)=1)+0

concurrently, where the second formula will become exponentionally slower with increasing range size...

My question is therefore, what woudl be the best way to go about this? Should I be using formulas at all? My intuition says that VBA might be better, alas I do not know any myself (although I am quite a proficient hacker of code I find online). I hope somebody might be able to point me in the right direction here, or supply some code which I can test out to pull this off? The 22,000 row range took almost an hour, so I know my code works, I think it is just horribly inefficient.

My specs are:
OS: Windows 7 Sp1
MS Office: 2010

Any and all help much appreciated.

Sample sheet below. Last two columns are my concatenated ID's which I would like to scan for first unique value, where detection of first instance of unique value would enter a "1" into new columns "Q" and "R" respectively.

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #666699"]SALNUM[/TD]
[TD="bgcolor: #666699"]SALNVN[/TD]
[TD="bgcolor: #666699"]SALINN[/TD]
[TD="bgcolor: #666699"]SALRDN[/TD]
[TD="bgcolor: #666699"]SALDVN[/TD]
[TD="bgcolor: #666699"]SALDYY[/TD]
[TD="bgcolor: #666699"]SALDMM[/TD]
[TD="bgcolor: #666699"]SALDDD[/TD]
[TD="bgcolor: #666699"]SALEGC[/TD]
[TD="bgcolor: #666699"]SALNYY[/TD]
[TD="bgcolor: #666699"]SALNMM[/TD]
[TD="bgcolor: #666699"]SALNDD[/TD]
[TD="bgcolor: #666699"]SALXTD[/TD]
[TD="bgcolor: #666699"]SALXTT[/TD]
[TD="bgcolor: #666699"]INVID[/TD]
[TD="bgcolor: #666699"]LINID[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7442[/TD]
[TD="bgcolor: #ccffff, align: right"]102619[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"]5256582[/TD]
[TD="bgcolor: #ccffff, align: right"]37438[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]15[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]14[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] * *74422015414[/TD]
[TD="bgcolor: #ccffff, align: right"]52565821[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"] * *7442[/TD]
[TD="align: right"]102619[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5256582[/TD]
[TD="align: right"]37438[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] * *74422015414[/TD]
[TD="align: right"]52565821[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7404[/TD]
[TD="bgcolor: #ccffff, align: right"]102620[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] *36989[/TD]
[TD="bgcolor: #ccffff, align: right"]37444[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]16[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]15[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] * *74042015415[/TD]
[TD="bgcolor: #ccffff, align: right"] *369891[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"] * *7404[/TD]
[TD="align: right"]102621[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] *37065[/TD]
[TD="align: right"]37445[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] * *74042015415[/TD]
[TD="align: right"] *370651[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7332[/TD]
[TD="bgcolor: #ccffff, align: right"]102622[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] *37466[/TD]
[TD="bgcolor: #ccffff, align: right"]37469[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]23[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]22[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] * *73322015422[/TD]
[TD="bgcolor: #ccffff, align: right"] *374661[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"] * *7332[/TD]
[TD="align: right"]102622[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] *37466[/TD]
[TD="align: right"]37469[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] * *73322015422[/TD]
[TD="align: right"] *374661[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7332[/TD]
[TD="bgcolor: #ccffff, align: right"]102623[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] 492823[/TD]
[TD="bgcolor: #ccffff, align: right"]37489[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]28[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]27[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] * *73322015427[/TD]
[TD="bgcolor: #ccffff, align: right"] 4928231[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"] * *7332[/TD]
[TD="align: right"]102624[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] *37499[/TD]
[TD="align: right"]37502[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] * *73322015430[/TD]
[TD="align: right"] *374991[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7332[/TD]
[TD="bgcolor: #ccffff, align: right"]102624[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] *37499[/TD]
[TD="bgcolor: #ccffff, align: right"]37502[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]5[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]4[/TD]
[TD="bgcolor: #ccffff, align: right"]30[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] * *73322015430[/TD]
[TD="bgcolor: #ccffff, align: right"] *374991[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"] * *7282[/TD]
[TD="align: right"]102625[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] *37041[/TD]
[TD="align: right"]37548[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] * *72822015518[/TD]
[TD="align: right"] *370411[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7282[/TD]
[TD="bgcolor: #ccffff, align: right"]102625[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] *37041[/TD]
[TD="bgcolor: #ccffff, align: right"]37548[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]5[/TD]
[TD="bgcolor: #ccffff, align: right"]19[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]5[/TD]
[TD="bgcolor: #ccffff, align: right"]18[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]2[/TD]
[TD="bgcolor: #ccffff, align: right"] * *72822015518[/TD]
[TD="bgcolor: #ccffff, align: right"] *370411[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"] * *7282[/TD]
[TD="align: right"]102626[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] *37041[/TD]
[TD="align: right"]37549[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] * *72822015518[/TD]
[TD="align: right"] *370411[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #ccffff, align: right"] * *7282[/TD]
[TD="bgcolor: #ccffff, align: right"]102626[/TD]
[TD="bgcolor: #ccffff, align: right"]1[/TD]
[TD="bgcolor: #ccffff, align: right"] *37041[/TD]
[TD="bgcolor: #ccffff, align: right"]37549[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]5[/TD]
[TD="bgcolor: #ccffff, align: right"]19[/TD]
[TD="bgcolor: #ccffff, align: right"]001[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]5[/TD]
[TD="bgcolor: #ccffff, align: right"]18[/TD]
[TD="bgcolor: #ccffff, align: right"]2015[/TD]
[TD="bgcolor: #ccffff, align: right"]2[/TD]
[TD="bgcolor: #ccffff, align: right"] * *72822015518[/TD]
[TD="bgcolor: #ccffff, align: right"] *370411[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You might try the MATCH function.
Note that the second MATCH refers to 1 row above.

=IF(MATCH(O2,$O$1:$O$14,0)=MATCH([COLOR="#FF0000"]O1[/COLOR],$O$1:$O$14,0),"",1)

There are also ways to use the FREQUENCY function to just get a count of unique items.

Excel Workbook
	K	L	M	N	O	P	Q	R
1	SALNMM	SALNDD	SALXTD	SALXTT	INVID	LINID
2	4	14	2015	1	* *74422015414	52565821	1	1
3	4	14	2015	1	* *74422015414	52565821
4	4	15	2015	1	* *74042015415	*369891	1	1
5	4	15	2015	1	* *74042015415	*370651		1
6	4	22	2015	1	* *73322015422	*374661	1	1
7	4	22	2015	1	* *73322015422	*374661
8	4	27	2015	1	* *73322015427	4928231	1	1
9	4	30	2015	1	* *73322015430	*374991	1	1
10	4	30	2015	1	* *73322015430	*374991
11	5	18	2015	2	* *72822015518	*370411	1	1
12	5	18	2015	2	* *72822015518	*370411
13	5	18	2015	2	* *72822015518	*370411
14	5	18	2015	2	* *72822015518	*370411
Sheet

 

[AhoyNC]

There could be an issue with the formula I posted above. If your invoice numbers and customer are grouped together like you have in your example the formula should work and could be changed to leave out the MATCh, however if they are not grouped you will get a bogus answer. Example if an invoice number is in rows 5,6,7, and then again on row 200 the one on row 200 will count as another invoice.
If that is not an issue then the formulas could be changed to:

=IF(O2=O1,"",1)
=IF(P2=P1,"",1)

If you want a count of unique invoices by customer them maybe the FREQUENCY formula in the example below.
NOTE - This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC)

Copy formula down and change ranges to match your data.

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T
1	SALNMM	SALNDD	SALXTD	SALXTT	INVID	LINID			LINID	Count
2	4	14	2015	1	* *74422015414	52565821			52565821	1
3	4	14	2015	1	* *74422015414	52565821			*369891	1
4	4	15	2015	1	* *74042015415	*369891			*370651	1
5	4	15	2015	1	* *74042015415	*370651			*374661	1
6	4	22	2015	1	* *73322015422	*374661			4928231	1
7	4	22	2015	1	* *73322015422	*374661			*374991	1
8	4	27	2015	1	* *73322015427	4928231			*370411	2
9	4	30	2015	1	* *73322015430	*374991
10	4	30	2015	1	* *73322015430	*374991
11	5	18	2015	2	* *72822015518	*370411
12	5	18	2015	2	* *72822015518	*370411
13	5	18	2015	2	* *72822015518	*370411
14	5	18	2015	2	* *72822015518	*370411
15					* *74042015415	*370411
Sheet

 

[e4xit]

Hi, thanks AhoyNC for your help. I think that having tried to hack your long (final) formula and failed, that the easiest way is for me to manually sort the incoming data by InvoiceID (after refresh) and use the somewhat simpler and faster

=IF(O2=O1,"",1)

formulas.

Theoretically there is a small possibility that invoice numbers could not be in adjacent rows as they come in (although in theory it should not happen), but I think this method will prove accurate enough for my needs right now. So, thanks again for your help.

Just one question though, the "back end" of the FREQUENCY formula seems to be able to "count and remember" if a value has appeared before in a list, could this same process be hijacked either through VBA or standard formula to do the same "count unique", but also just to put a 1 in adjacent cell for each first instance? This seems like by far the closest natural function operation to what I need, but just needs a small tweak.

 

[AhoyNC]

Here is a formula based on one from Aladin Akyurek that I think will do what you want.
Change ranges to match your data and copy down rows.
This is an array formula and must be entered with

Excel Workbook
	K	L	M	N	O	P	Q	R
1	SALNMM	SALNDD	SALXTD	SALXTT	INVID	LINID
2	4	14	2015	1	* *74422015414	52565821	1	1
3	4	14	2015	1	* *74422015414	52565821
4	4	15	2015	1	* *74042015415	*369891	1	1
5	4	15	2015	1	* *74042015415	*370651		1
6	4	22	2015	1	* *73322015422	*374661	1	1
7	4	22	2015	1	* *73322015422	*374661
8	4	27	2015	1	* *73322015427	4928231	1	1
9	4	30	2015	1	* *73322015430	*374991	1	1
10	4	30	2015	1	* *73322015430	*374991
11	5	18	2015	2	* *72822015518	*370411	1	1
12	5	18	2015	2	* *72822015518	*370411
13	5	18	2015	2	* *72822015518	*370411
14	5	18	2015	2	* *72822015518	*370411
15					* *74042015415	*369891
CTRL-SHIFT-ENTER.