# of unique entries across 2 tabs (excluding duplicates and empty cells)

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
209
Office Version
  1. 365
Platform
  1. Windows
Hi,
This one is a bit complicated... I currently have a working array formula to look up the # of unique dates on one single tab in line with pre-defined criteria:
{=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),'Tab one'!$F$13:$F$50000),1))}

Can I have this formula working across two tabs (let's say another one will be called Tab two), looking into exactly the same ranges and following the same logic? As an option, totally different formula will work just as well, as long as it returns valid results.
Please help :)
 
Ok thanks. I think this is it for the moment to cover this component.
The remaining 2 issues are as follows. Pasting the formula again:
=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000),ROW(INDIRECT("1:60000"))),1))
I have 2 date ranges on 2 separate tabs, they are partially overlapping. Both of these ranges are in column F on two tabs, and at the moment there's nothing else apart from dates in these columns.

Issue #1. If tab 1 has zero entries corresponding to criterion noted in cell B4, regardless of the # of unique dates on tab 2, formula returns zero. While it should return # of unique entries on tab 2 (only; because tab 1 has none).
Issue #2. The formula doesn't seem to be cross-checking unique dates across the two tabs in principle (and it should). So if tab 1 has 01 Sep 20, 02 Sep 20, and 03 Sep 20, while tab 2 has 02 Sep 20, 03 Sep 20, 04 Sep 20, and 05 Sep 20 (for a given criterion in cell B4), I would want the formula to return "3". Is it possible?

Please help....
 
Last edited:
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
This seems to be a different idea than your original request. UNIQUE generally refers to the number of distinct items, and in your latest example, that would be 01 Sep, 02 Sep, 03 Sep, 04 Sep and 05 Sep. What you're now asking is for the number of unpaired dates. Doable, but a much different formula. Is that the case? Is it possible to have the same date multiple times on the same sheet?
 
Upvote 0
Apologies Eric W, please ignore my previous post. Sometimes right questions are enough to put me back on track :)
The overall idea, and the formula in use, are still the same. What did confuse me is that when I selected a different column in my source data, the formula ceased to work and consistently returned zero. So here it goes for easy reference:
=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000),ROW(INDIRECT("1:60000"))),1))
Also attaching the screenshot from my source data. The formula works for column F - but not for column M (more text under the clip)
Capture.PNG
Upon closer look, I see that the dates in both columns look different. Column F has them aligned to the left whereas column M has them aligned in the center. I thought this was just the formatting issue and set both columns manually in the same manner (DD-MMM-YYYY, alignment to the left) - it changes nothing for the output. Then I copied formatting from column F and pasted it onto column M => still doesn't work. However when I copy several cells from column F and paste them "as is" into column M, formula starts "recognizing" them in column M. What am I doing wrong please?
 
Upvote 0
It sounds like the difference is that the F column is a date (number) which is formatted to look like 20-Aug-2020. But column M is a text value. The formula I gave you ignores text values. Copying the formatting from F to M didn't work since the values of column M were already set. There are a few options here. First, you can try changing the formula like this:

=SUM(IF(FREQUENCY(IF('Tab one'!$E$13:$E$50000=B4,'Tab one'!$F$13:$F$50000+0),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Tab two'!$E$13:$E$50000=B4,'Tab two'!$F$13:$F$50000)+0,ROW(INDIRECT("1:60000"))),1))

Adding 0 to a text representation of a number, even a date, converts it to an actual number.

The other way is to convert the text dates to real dates in column M. There are a few ways to do that. You could put 0 in a cell, copy it, then select the data in column M, Paste-Special-Add. Which will add the 0, making them real numbers. You may need to reapply the date formatting at that point.
 
Upvote 0
Thank you Eric W, this is awesome, works like a charm! Special thanks for explanations as to how it works.
On a separate note, I would have never figured out that +0 in the first instance above should go before the bracket, and in the second instance it goes after!
 
Upvote 0
On a separate note, I would have never figured out that +0 in the first instance above should go before the bracket, and in the second instance it goes after!
Well, that was just me being sloppy! I'd actually recommend putting both of them before the bracket. The internal processing is actually a little different, but in this case it just happened to work out the same.

Glad it works for you!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top