Method / Formula to List Unique Cell/Rows + How Many Times Each Appears in Data

This is an array formula. They are resource heavy. Expect that many rows of data to slow workbook calculations. You might reduce that load somewhat by pre-concatenating those fields in a separate column. Here they are done in formula.

Array entered in F2 filled down and across until you get blanks.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I
1	ColumnA	ColumnB	ColumnC	ColumnD		ColumnA	ColumnB	ColumnC	ColumnD
2	Event#1	Value#1	Detail#1	ID#1		Event#1	Value#1	Detail#1	ID#1
3	Event#2	Value#2	Detail#2	ID#2		Event#2	Value#2	Detail#2	ID#2
4	Event#1	Value#1	Detail#1	ID#1		Event#3	Value#2	Detail#2	ID#2
5	Event#3	Value#2	Detail#2	ID#2
6	Event#2	Value#2	Detail#2	ID#2

Another way: A non array non concatenated approach is to use some helper columns.

Here I used the fill series feature to set row numbers a little beyond anticipated need. Then in F2 and filled down identifies the 1st of each unique combination and assigns a row number to it.

Formula:

Please Login or Register  to view this content.

In H2 filled down and across until you get blanks.

Formula:

Please Login or Register  to view this content.

Then to get the counts of each in L2 and filled down.

Formula:

Please Login or Register  to view this content.

A

B

C

D

E

F

G

H

I

J

K

L

1

ColumnA

ColumnB

ColumnC

ColumnD

ColumnA

ColumnB

ColumnC

ColumnD

2

1

Event#1

Value#1

Detail#1

ID#1

1

Event#1

Value#1

Detail#1

ID#1

2

3

2

Event#2

Value#2

Detail#2

ID#2

2

Event#2

Value#2

Detail#2

ID#2

2

4

3

Event#1

Value#1

Detail#1

ID#1

FALSE

Event#3

Value#2

Detail#2

ID#2

1

5

4

Event#3

Value#2

Detail#2

ID#2

4

6

5

Event#2

Value#2

Detail#2

ID#2

FALSE

7

6

8

7

9

8

10

9

11

10

Dave has beaten me too it, but I am posting this as the approach is a little different.

Rather than computing the unique rows by formula I have done it procedurally using the "Remove duplicates" data tool. The advantage of this approach is that the formulas involved are considerably simpler, the disadvantage is the added manual steps and the need to repeat them if this is not a "one-off" exercise.

I started a new workbook and replicated your posted values in columns A:D.
In col-E I created a unique ID simply by concatenating all 4 columns. In E1 and copied down:

Formula:

Please Login or Register  to view this content.

Col-F is a count of how many times each UID occurs. In F2 and copied down:

Formula:

Please Login or Register  to view this content.

To get a list of UIDs I have assumed this is a "one-off" exercise and done it procedurally rather than by formula:

• Create a new worksheet "UniqueRows"
• Copy/Paste the sheet1!col-E into UniqueRows!A1
• Select A1
• On the ribbon: Data > Remove Duplicates
• Select "My data has headers" then OK

You now have a list of unique UIDs.

See the attached workbook.

Good Afternoon -

I really appreciate your reply, FlameRetired. My data from the larger spreadsheet has 5396 rows, so copied the formula you provided above, then replaced all instances of $6 with $5396. Finally, I pasted it into F2 of the spreadsheet. It highlighted the first 4 columns as shown in the screenshot, however; when clicking out of F2 the cell was left empty. I drug the cell to replicate the formula over and down a few cells, but still nothing was populated. Clicking back in F2, I see the formula, so know it saved. Below is also the formula I used in F2:

Please Login or Register  to view this content.

I also tried the formula exactly as you posted it to test just in the first few rows, but got the same result. Am I doing something incorrectly?
excel1.png

Thank You

By the way, I tried your 2nd suggestion as well. After placing the formula in F2, then double clicking lower right of cell to replicate it down; I found that all cells in F stated "FALSE". I then started by putting the 2nd formula in H2 as instructed and replicated it down a few, but all of those came up empty. Not sure if I'm doing something incorrectly since this one also isn't working or what. Please advise -
excel2.jpg

Thank You

UPDATE

I realized after posting that I had failed to go back and make an empty A column prior to testing. I did so plus deleted the extra column (E) containing all the same data, then used your formula in column F which was the next blank one. Unfortunately, I got the same results as shown below with all showing "FALSE" and column A empty. Any suggestions? Thanks
excel4.jpg

Thanks for replying, GeoffW283 -

I see what you are doing so gave it a shot. Quick note, I realize my data has an extra column than my example, but all values in it are common so I deleted it when trying yours...

The first part worked without issue as in column E it created a value that combined all cells. Unfortunately, when I started with the 2nd formula in column F; all results were "#VALUE!". I tried copying column E, pasting it into a new column as values, then replacing column E so that F would be referencing values instead of a formula's results (didn't know if that would make a difference), but got the same results as shown below:
excel3.jpg

Any suggestions? Thank You

The only way I can think of to get the countif() in col-E to return a #VALUE! error is if the total length of the string in col-E is greater than 256 characters. I can't tell from your posted picture, but is that what is happening? If so, then do you need a concatenation of all four columns to form a unique string or is there a subset of columns that would still be unique but not exceed 256 characters? If this isn't the reason for the #value! error then please post a subset of your workbook - just enough to reproduce the #value! error.

I agree with Geoff.

The time has arrived for a workbook upload.

If you do not know how to do this:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

I finally got it working - Thanks!!! Turns out the count of not only the combination of the cells, but one of the cells alone was over 900 on average so just compared the event names from column A itself.

I really appreciate your help - just have one more question if possible, please...

So now I have two spreadsheets which each have two columns (that count) listing event names in one and the # of times the event appeared in the other. Roughly 90% of the events on each sheet have the exact same name, but about 10% are not on the other. My final task is to create a list of all unique events between the two sheets which list the difference between each of their counts. Events which are on only one sheet would be 0 obviously.

Would this be doable? Again, I really appreciate your assistance!! Thanks!

UPDATE

I think I'm good now as since posting, I did a couple of things manually so now have a single spreadsheet as follows with A containing a list of all unique event names, B the total of that event from sheet 1, C a total of that event from sheet 2, and D where I plan to put the comparison between the two. I think I'll just subtract one from the other then know which had the positive difference if the result is negative or not.
Column A Column B Column C Column D
Event Name A 100 150
Event Name B 109 90
...

Thanks again!

Glad you found solution. Thank you for updating us.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.