Hi,
Required a formula help (office 365/Old version both) for summarize a filtered unique records with no blanks and no duplicates, as based on data contains in column (A to G).
sample workbook attached.
Thanks for the help.
Hi,
Required a formula help (office 365/Old version both) for summarize a filtered unique records with no blanks and no duplicates, as based on data contains in column (A to G).
sample workbook attached.
Thanks for the help.
Try in I3:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).Please Login or Register to view this content.
Drag down and accross
Quang PT
Excel 2010 + check duplicate for Consumer, BS Cont, Activity
I2:Oxx
=IFERROR(INDEX(A$3:A$19,AGGREGATE(15,6,ROW($A$3:$A$19)/(MATCH($A$3:$A$19&$B$3:$B$19&$C$3:$C$19,$A$3:$A$19&$B$3:$B$19&$C$3:$C$19,)=ROW($A$3:$A$19)-ROW($A$2))/($A$3:$A$19>0),ROWS(I$3:I3))-ROW($A$2)),"")
MS365
=UNIQUE(FILTER(A3:G19,A3:A19>0))
Power Query 2010+
Please Login or Register to view this content.
If you are really using Excel in Office 365, you should have FILTER and UNIQUE functions. If so,
I3:
Formula:Please Login or Register to view this content.
which should spill into I3:M10 given your sample data. No blank lines, and distinct COMBINATIONS of values from columns A to E.
N3:
Formula:Please Login or Register to view this content.
which should spill into N3:N10. Select N3:N10, copy, move to O3, and paste.
If you need this also for older versions of Excel, then it's rather more complicated. It also requires more cells per result.
Q3:
Formula:Please Login or Register to view this content.
Q4:
Formula:Please Login or Register to view this content.
Fill Q4 down until it returns #N/A.
R3:
Formula:Please Login or Register to view this content.
Fill R3 down into the same rows as the table in columns A to G, so given your sample data, into R4:R19.
The formulas in column Q from cell Q3 down are the row indices for the distinct columns A to E records in columns A to G.
I3:
Formula:Please Login or Register to view this content.
Fill I3 right into J3:M3. Then select I3:M3 and fill down until the formulas return #N/A.
N3:
Formula:Please Login or Register to view this content.
Fill N3 right into O3. Select N3:O3 and fill down to match the valid records in columns I to M.
Or you could use a pivot table.
Last edited by hrlngrv; 10-21-2020 at 03:42 AM. Reason: removing unnecessary Q1:Q2 formulas
Picky: A3:A19>0 would be true for cells in A3:A19 which evaluated to "" rather than blank since Excel treats ALL text as greater than ALL numbers.
Thanks for all for your support and different ways of solutions.
Bo_Ry's excellent 3 way solution(Old versions/365/Power query).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks