Report to be generated from database for Unique Values

[kundanlal]

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 Code	Green Group	Blue Group	Others	Total
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

 

[herofox]

here you are,it should be By Countifs

 

[kundanlal]

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 Code	Green Group	Blue Group	Others	Total
12950	9	10	19	38

Regards,

Kundanlal

 

[kundanlal]

I am using MS Excel 2007..

 

[Harshawardhan_Shastri]

I have used pivot table to answer your query. Secondary pivot based on primary pivot. Pl check attachment.Hope this what you looking for.

 

[kundanlal]

Appreciated.. Thanks Harshawardhan..

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

 

[herofox]

TRY THIS

 

[kundanlal]

Very much appreciated.. Many Thanks..

 

[bosco_yip]

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

 

[kundanlal]

Appreciated .. Thans Bosco..

Regards,

Kundanlal

 

[kundanlal]

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]

Hi ,

If you can upload your revised workbook , I can try and help.

Narayan

 

[kundanlal]

Hi ,

If you can upload your revised workbook , I can try and help.

Narayan

Thanks Narayanji.

Enclosed pleaase find database.. Please see Sheet 2..

 

[NARAYANK991]

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

 

[NARAYANK991]

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

 

[kundanlal]

Thanks Narayanji..Appreciated your help..

 

[rahulshewale1]

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/ar...or-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

 

[kundanlal]

Thanks Rahulji..Appreciated your help..Will try this..