SOLVED
Home

Extracting data from a list without duplicate (repeating entry)

%3CLINGO-SUB%20id%3D%22lingo-sub-650994%22%20slang%3D%22en-US%22%3EExtracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650994%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20my%20template%20creation%20with%20regards%20to%20extracting%20data%20from%20another%20sheet%20with%20multiple%20criteria.%20I%20hope%20somebody%20could%20provide%20solution%20to%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20file%20attached%2C%20sheet2%20contains%20the%20raw%20data%20and%20sheet1%20serves%20as%20the%20summary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20on%20how%20to%20retrieve%20all%20the%20discipline%2C%20role%2C%20level%2C%20hourly%20rates%20without%20repetition%20due%20to%20different%20values%20in%20working%20hours%2C%20days%2C%20quantity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20now%20it%20retrieves%20all%20the%20entry%20as%20long%20as%20it%20is%20unique%20in%20terms%20of%20the%20whole%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20768px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116203i3D4B2763B083A0F0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20query%201.PNG%22%20title%3D%22Excel%20query%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20is%20the%20snap%20shot%20of%20the%20summary%20but%20I%20would%20like%20to%20retrieve%20instead.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20765px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116204i415CFF8740CA51EF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20query%202.PNG%22%20title%3D%22Excel%20query%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBasically%20it%20should%20retrieve%20only%20one%20entry%20for%20discipline%2C%20role%2C%20level%20and%20rate%20and%20calculate%20the%20rest%20for%20the%20hours%2C%20days%20and%20fee.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-650994%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECOUNTIFS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eextracting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elist%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eno%20duplicate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Erow%20function%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651711%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330179%22%20target%3D%22_blank%22%3E%40MrNobody%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20do%20that%20by%20Power%20Query%20-%20remove%20names%2C%20group%20by%20columns%20which%20are%20not%20aggregated%20and%20sum%20the%20rest.%20Please%20see%20Sheet3%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654219%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20users%20of%20the%20template%20are%20not%20familiar%20with%20Power%20Query%20or%20any%20advance%20stuff%20and%20could%20only%20do%20little%20editing%20i.e.%20filter%2C%20group%2C%20hide%20row%2Fcolumn%2C%20color%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20I%20thought%20of%20using%20codes%20to%20make%20it%20flexible%20but%20there%20is%20one%20problem%20I%20have%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20%22Activity%20Fee%22%20tab%2C%20how%20can%20I%20make%20it%20that%2C%20whatever%20the%20activity%20code%20I%20put%2C%20it%20will%20retrieve%20only%20the%20codes%20associated%20to%20that%20activity%20(with%20no%20duplicates)%20from%20the%20%22Internal%20Calculation%22%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20found%20a%20formula%20that%20can%20retrieve%20only%20unique%20values%20but%20it%20is%20for%20the%20whole%20column%2C%20I%20wanted%20to%20add%20a%20criteria%20to%20it%20which%20is%20the%20activity%20code%20value%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654744%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330179%22%20target%3D%22_blank%22%3E%40MrNobody%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20what%20end%20user%20shall%20know%20about%20Power%20Query%20is%20how%20to%20use%20Refresh%20button.%20I%20guess%20they%20are%20not%20familiar%20with%20template%20formulas%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnyway%2C%20to%20select%20unique%20items%20with%20additional%20filter%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX('Internal%20Calculation'!A%242%3AA%24300%2CMATCH(1%2C%20INDEX(('Internal%20Calculation'!B%242%3AB%24300%3D%24C%242)*(COUNTIF(A%2410%3AA10%2C'Internal%20Calculation'!A%242%3AA%24300)%3D0)%2C0)%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EPlease%20note%2C%20that's%20not%20an%20array%20formula.%20It's%20in%26nbsp%3BActivity%20fee%20(2)%20sheet%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654745%22%20slang%3D%22en-US%22%3ERE%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654745%22%20slang%3D%22en-US%22%3EHow%20do%20I%20create%20a%20post%20on%20this%20page%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654760%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351009%22%20target%3D%22_blank%22%3E%40Anekapb%3C%2FA%3E%26nbsp%3B%2C%20if%20that's%20a%20new%20topic%20please%20go%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%20and%20start%20new%20conversation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20the%20same%20topic%20you%20already%20make%20a%20post%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657130%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20data%20from%20a%20list%20without%20duplicate%20(repeating%20entry)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20finally%20works%20!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
MrNobody
Occasional Contributor

Hi Community,

 

I need help with my template creation with regards to extracting data from another sheet with multiple criteria. I hope somebody could provide solution to my problem.

 

On the file attached, sheet2 contains the raw data and sheet1 serves as the summary.

 

My problem is on how to retrieve all the discipline, role, level, hourly rates without repetition due to different values in working hours, days, quantity.

 

Right now it retrieves all the entry as long as it is unique in terms of the whole row.

 

  Excel query 1.PNG

Here is the snap shot of the summary but I would like to retrieve instead.Excel query 2.PNG

Basically it should retrieve only one entry for discipline, role, level and rate and calculate the rest for the hours, days and fee.

 

 

 

6 Replies
Highlighted

@MrNobody , you may do that by Power Query - remove names, group by columns which are not aggregated and sum the rest. Please see Sheet3 attached.

@Sergei Baklan 

 

Thanks for your reply

 

The problem is that users of the template are not familiar with Power Query or any advance stuff and could only do little editing i.e. filter, group, hide row/column, color format.

 

By the way, I thought of using codes to make it flexible but there is one problem I have

 

In the "Activity Fee" tab, how can I make it that, whatever the activity code I put, it will retrieve only the codes associated to that activity (with no duplicates) from the "Internal Calculation" tab.

 

Currently I found a formula that can retrieve only unique values but it is for the whole column, I wanted to add a criteria to it which is the activity code value above.

Solution

@MrNobody 

Hi,

 

In general what end user shall know about Power Query is how to use Refresh button. I guess they are not familiar with template formulas as well.

 

Anyway, to select unique items with additional filter could be like

=IFERROR(INDEX('Internal Calculation'!A$2:A$300,MATCH(1, INDEX(('Internal Calculation'!B$2:B$300=$C$2)*(COUNTIF(A$10:A10,'Internal Calculation'!A$2:A$300)=0),0),0)),"")

Please note, that's not an array formula. It's in Activity fee (2) sheet attached.

How do I create a post on this page?

@Anekapb , if that's a new topic please go to https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral and start new conversation.

 

If that's the same topic you already make a post here.

@Sergei Baklan

 

Thank you very much

 

It finally works !! 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies