• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Report to be generated from database for Unique Values

kundanlal

Member
Hi..

Enclosed file with Database and Report to be generated.

Here The database may run in 5000 to 1000 rows.

I need to generate Report as under

Out Put Report to be Generated
Emp CodeGreen GroupBlue GroupOthersTotal
13637​
11​
9​
6​
26​
12950​
9​
10​
18​
37​
10670​
12​
6​
0​
18​

I have tried using Sumproduct with counif foruma, but it is giving me error and the time taken was too much. Wasted lot of time with no result.

Can anyone help me in this please..

Thanks

Regards,

Kundanlal
 

Attachments

kundanlal

Member
Thanks.. The Report so generated are counting of what ever items (irespective of repeatation) available in column A for given condition.

However, the requirement is to Generate Report on the basis of ...

Unique Values in Column A for Emp Code=12950 and Customer Type=Green Group or Blue Group or Others.. Reports will look like

Out Put Report to be Generated
Emp CodeGreen GroupBlue GroupOthersTotal
129509101938

Regards,

Kundanlal
 

kundanlal

Member
Appreciated.. Thanks Harshawardhan..

But, I need formula base solution as the result/outcome is so generated is small part of my other report.
 

bosco_yip

Excel Ninja
Another option

Multiple conditional count unique formula solution without helper columns

In G6, copied across and down :

=SUMPRODUCT(($C$4:$C$900=$F6)*($B$4:$B$900=G$5)/COUNTIFS($C$4:$C$900,$C$4:$C$900&"",$B$4:$B$900,$B$4:$B$900&"",$A$4:$A$900,$A$4:$A$900&""))

Regards
Bosco
 

Attachments

Last edited:

kundanlal

Member
Another option

Multiple conditional count unique formula solution without helper columns

In G6, copied across and down :

=SUMPRODUCT(($C$4:$C$900=$F6)*($B$4:$B$900=G$5)/COUNTIFS($C$4:$C$900,$C$4:$C$900&"",$B$4:$B$900,$B$4:$B$900&"",$A$4:$A$900,$A$4:$A$900&""))

Regards
Bosco
------------------------------------------
Dear Sir,

The formula worked fine for entire table. However, now if I add dates in column say (I) in table and if need to generate the report monthwise, how do I go for that.. i.e. for June 19, July 19, August 19, Sept 19 etc., means now unique values under each of these months.. can you please help me.. I tried adding following..formula not working.. I5 to o5 are Row headings dates-->>(June 2019.. etc)

=SUMPRODUCT((Month($i$4:$i$900=l$5)*($C$4:$C$900=$F6)*($B$4:$B$900=G$5)/COUNTIFS((Month($i$4:$i$900=l$5)),(Month($i$4:$i$900=l$5))&"",$C$4:$C$900,$C$4:$C$900&"",$B$4:$B$900,$B$4:$B$900&"",$A$4:$A$900,$A$4:$A$900&""))

Thanks and Regards,
 

NARAYANK991

Excel Ninja
Hi ,

See the attached file.

I have converted your data range into a table so that structured references can be used.

I have also added a column to your data , so that the same COUNTIFS construct can be used.

Narayan
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

If you do not want to use an additional column in your table , try this formula :

=SUM(--(FREQUENCY(IF(Table1[Emp Code] = $M6, IF(Table1[Customer Type] = N$5, IF(TEXT(Table1[Date], "mmm") = TEXT($N$4, "mmm"), MATCH(Table1[Product Code],Table1[Product Code],0)))),MATCH(Table1[Product Code],Table1[Product Code],0))>0))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 

rahulshewale1

Active Member
Hi @kundanlal ,

You are interested in Power Pivot or Power Query Solution . It is easy and Improve spreadsheet performance .

Power Pivot Solution Steps

1) Convert data into table
2) Go to Power Pivot Tab > Add to Data Model >Type Below Formula in Measure Grid

Unique Count:=DISTINCTCOUNT(Database[Product Code])

Then

Drag Emp Id in Row Labels > Colour In Columns Label > Drag Measure in Value Area >



https://support.office.com/en-us/article/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8

Syntax

DISTINCTCOUNT(<column>)



Power Query Solution

1) Convert Data In Table ( Ctrl + T )
2) Go To Power Query Tab > From Table Or Range > Remove Date Column Only > Keep only Month ( "Product Code", "Customer Type", "Emp Code", "Month")

3 ) Select Select All Columns > Go to Home > Remove Duplicate > Group by > Given Column Name > Operation Select Count > Ok

4 ) Power Query Output Table Appereed and insert Pivot table >
Then
Drag Emp Id in Row Labels > Colour In Columns Label > Drag CountColumn in Value Area ( Sum )

Regard
Rahul shewale
 

Attachments

Top