Counting and displaying Uniqe Values(Countries) (Network Steve Forum)

Counting and displaying Uniqe Values(Countries)

I have a Large table, Country, Project, Month(Jan, Feb, March.....)

I Count Projects each Month based on simple Formula.

Yes/No = Removed

No/Yes = Added

Yes/Yes= No Changes

No/No = None

Projects are all fine. i got all in and outs. (COUNTIF[Range]"Value") and (SUMIF"Value"[Range])

I count Unique Countries

=SUM(IF(A3:A10031<>"",1/COUNTIF(A3:A10031,A3:A10031)))-(CTR-SHIFT-ENTER)

How can i get the In(Added) and Outs(Removed) of the Unique Values of Countries based on Projects Answers?

Country

Project Num

Jan-14

Feb-14

Mar-14

Apr-14

May-14

Jun-14

Jul-14

Aug-14

Sep-14

Oct-14

Nov-14

Dec-14

Angola

AGO 1

Yes/Yes

Yes/No

No/No

Ghana

GHA 8

No/No

No/Yes

Yes/Yes

Ghana

GHA 1

No/Yes

Yes/Yes

Yes/No

No/No

Ghana

GHA 3

Yes/Yes

Yes/No

No/No

Ghana

GHA 5

No/Yes

Yes/Yes

Yes/No

No/No

Ghana

GHA 7

No/No

No/Yes

Yes/Yes

Ghana

GHA 2

Yes/Yes

Ghana

GHA 6

No/No

No/Yes

Yes/Yes

Kenya

KEN 5

Yes/Yes

Yes/No

No/No

Kenya

KEN 3

Yes/Yes

Yes/No

No/No

Kenya

KEN 4

Yes/Yes

Yes/No

No/No

Kenya

KEN 6

Yes/No

No/No

Kenya

KEN 6

No/No

No/Yes

Yes/Yes

Yes/No

No/No

Kenya

KEN 7

No/No

No/Yes

Yes/Yes

Yes/No

No/No

Mauritius

MRU 1

No/Yes

Yes/Yes

Yes/No

No/No

Morocco

MAR 2

Yes/Yes

Yes/No

No/No

Mozambique

MOZ 1

Yes/Yes

Yes/No

No/No

Mozambique

MOZ 2

Yes/Yes

Yes/No

No/No

Mozambique

MOZ 3

No/No

No/Yes

Yes/Yes

Yes/No

No/No

Mozambique

MOZ 4

No/No

No/Yes

Yes/Yes

Yes/No

No/No

Namibia

NAM 1

Yes/No

No/No

Nigeria

NGA 2

Yes/No

No/No

January 24th, 2015 5:29pm

Which Excel version do you use?
If you have Office 2013 Professional Plus, Office 365 Pro Plus, or Excel Standalone editions, you should consider using Power Query Add-In to get your report.

With Power Query you can transform the data above, unpivot it and load it to the worksheet. Then with PivotTable you can get anything you need.

With Power Query, I took your data above and unpivot it into this table:

Then I created a PivotTable to count how many projects you got by month (With PivotTable you can achieve many other reports on top of this kind of data).

If needed, I can provide step-by-step instructions, once you install Power Query.

Proposed as answer by Gil RavivMicrosoft employee Sunday, January 25, 2015 8:28 AM
Edited by Gil RavivMicrosoft employee Sunday, January 25, 2015 8:30 AM
Unproposed as answer by Eugene Laho 6 hours 31 minutes ago

Free Windows Admin Tool Kit Click here and download it now

January 25th, 2015 10:41am

Excel 2010 with free Power Query Add-In.
Compatible with Office 2013 Pro Plus.
With all the Power Query steps shown.
http://805fbd0dee7e8ba6e8e2-24c8ffd6d2ff5b45792f1baccf09ac8d.r61.cf1.rackcdn.com/01_25_15.xlsx

January 26th, 2015 1:35am

Hi Fil,

Thanks but im not trying to get the projects out, I get that simply with the formula below:

=IFERROR(INDEX($H$2:$S$2,,MATCH("Yes/No",$H4:$S4,0)),"")

And :

=COUNTIF(K3:K231, "No/Yes")......

Im Trying to get Distinct Count Countries and Display them. So far trying to work out this formula:

=SUM(IF(FREQUENCY(IF($A$2:$A$23<>"",IF($C$2:$N$23=P$1,MATCH($A$2:$A$23,$A$2:$A$23,0))),ROW($A$2:$A$23)-ROW($A$2)+1),1))

=IFERROR(INDEX($A$2:$A$23,SMALL(IF(FREQUENCY(IF($A$2:$A$23<>"",
    IF($C$2:$N$23=P$1,MATCH($A$2:$A$23,$A$2:$A$23,0))),
    ROW($A$2:$A$23)-ROW($A$2)+1),ROW($A$2:$A$23)-ROW($A$2)+1),
    ROWS(P2:P$3))),"")

Free Windows Admin Tool Kit Click here and download it now

January 26th, 2015 3:35am

Hi Eugene,

Do you want to have a list of countries with added/removed projects?

With PivotTable you can do it. The tricky part is to unpivot the original data with Power Query.

January 26th, 2015 9:33am

Hi Gil,

I want just a Distinct list of Countries, No Projects.

Free Windows Admin Tool Kit Click here and download it now

January 26th, 2015 8:16pm

=INDEX($A$2:$A$23, MATCH(0, COUNTIF($S$1:S1, $A$2:$A$23), 0))

Can display all the Unique Countries, last puzzle add a Yes/No Condition and get it by month.

January 26th, 2015 8:23pm

Hi Eugene,

This is the solution I was referring to:

If this is relevant for you, you can check out the workbook that Herbert shared above, and create the desired PivotTable with the distinct countries.

Free Windows Admin Tool Kit Click here and download it now

January 26th, 2015 8:35pm

Hi Gil,

This is what the desired result should be

Cut

Angola

Kenya

Mauritius

Morocco

Mozambique

Namibia

Nigeria

Add

Mauritius

January 28th, 2015 5:12pm

Hi Eugene,

Did you resolve the issue then?

I was proposing as an alternative for complex formulas that you consider to use the unpivot functionality of Power Query and then use PivotTable to get your desired results.

Free Windows Admin Tool Kit Click here and download it now

January 28th, 2015 6:54pm

Dear Gil, your proposal didnt give the desired results as i specifically asked Countries(based on Project answers)

So after all, probably you didn't understand the question.

Anyway thank you for your time but your solutions are not what im looking for.

Edited by Eugene Laho 6 hours 33 minutes ago

January 31st, 2015 8:49pm

This topic is archived. No further replies will be accepted.