Jun 16 2020 08:43 AM - edited Jun 16 2020 08:54 AM
Hello all,
On a second sheet I'm looking to extract only the rows where a USER ID has ONLY ONE "ANI" status, in this case the only rows that meet that criteria are for "Raj Singh" and "Mr Goerge"
USER ID | NAME | STATUS |
11 | Jane Smith | ANI |
11 | John Smith | ANI |
15 | Raj Singh | ANI |
17 | Debbie Downer | NIN |
17 | Donald Downer | ANI |
21 | Lee Nguyen | ANI |
21 | Chun Lee Nguyen | ANI |
22 | Fransisco Galvez | NIN |
22 | Franchesca Galvez | ANI |
25 | Mr George | ANI |
I have attached the sample workbook, a little help please?
Jun 16 2020 08:50 AM
Jun 16 2020 08:53 AM
That is CORRECT. However, i want ONLY the USER IDs that ONLY have ONE ANI @peteryac60
Jun 16 2020 09:49 AM
@shade206 Your criteria may have been conveyed incorrectly, as in the case below, the rows that meets the criteria.
Criteria 1.
- the rows where a USER ID has ONLY ONE "ANI" status is as shown below;
USER ID NAME STATUS
15 | Raj Singh | ANI |
17 | Donald Downer | ANI |
22 | Franchesca Galvez | ANI |
25 | Mr George | ANI |
Maybe the criteria you meant is as below
Criteria 2.
- User ID that appears once AND that has ONLY ONE "ANI" status.
if this is the criteria then you will only return the rows as shown below
USER ID NAME STATUS
15 | Raj Singh | ANI |
25 | Mr George | ANI |
See the attached
I have added 2 new sheet(Criteria 1 and Criteria 2) that gives you both results. I designed it using Power Query advance Group By function, for every change you make to Sheet1 you need to click Refresh to have it reflected to Criteria 1 and Criteria 2 sheets.
Jun 16 2020 10:10 AM
@ElElyon while i really appreciate you taking the time to do this, i don't know exactly what you did to achieve this, i just have the results i desired.
I'd need to know what you did in order to recreate it myself against my actual data.
Jun 16 2020 10:33 AM
Here is an alternative solution - add an extra column and count the duplicates , any item with 1 is unique so you can then extract as required.
hope that helps.
Peter
Jun 16 2020 10:53 AM
I am glad it solved your problem. I will share the code below, if it still does not make sense.
Let me know, which of the criteria you wanted. And I could look into doing a quick video for you.
for Criteria 1 see the m code below.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"USER ID", Int64.Type}, {"NAME", type text}, {"STATUS", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "ANI")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"USER ID"}, {{"Count", each Table.RowCount(_), type number}, {"New", each _, type table [USER ID=number, NAME=text, STATUS=text]}}),
#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"New"}),
#"Expanded New" = Table.ExpandTableColumn(#"Removed Other Columns", "New", {"USER ID", "NAME", "STATUS"}, {"USER ID", "NAME", "STATUS"})
in
#"Expanded New"
For the Criteria 2 see the m code below.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"USER ID", Int64.Type}, {"NAME", type text}, {"STATUS", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"USER ID"}, {{"Count", each Table.RowCount(_), type number}, {"New", each _, type table [USER ID=number, NAME=text, STATUS=text]}}),
#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"New"}),
#"Expanded New" = Table.ExpandTableColumn(#"Removed Other Columns", "New", {"USER ID", "NAME", "STATUS"}, {"USER ID", "NAME", "STATUS"})
in
#"Expanded New"
Cheers!
Jun 16 2020 12:46 PM
@ElElyon Thank you for taking the time, i'm not familiar with Power Queries or any of this, is there are way to do it just via Formula in a cell?
Jun 16 2020 01:21 PM
With formula
you may use in E2
=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,1/(COUNTIFS($A$2:$A$11,$A$2:$A$11)=1)*(ROW($A$2:$A$11)-ROW($A$1)),ROW()-ROW($E$1))),"")
drag it to the right and all down till empty cells appear.
Jun 16 2020 03:05 PM
@shade206 Another simple way to achieve this using excel and without going the Power Query path, will be to use the "Filter" function in excel
You should first go to YouTube and watch a quick video on how to use the Filter function, so what I am showing you below make more sense.
I have added a sheet called "Result" to the attached excel file.
the sheet uses the following formula in any cell.
=FILTER(Table1,(Table1[STATUS]="ANI")*(COUNTIFS(Table1[USER ID],Table1[USER ID])=1),"Not Found")
Formula Usage
=Filter(array or table , Condition , Alternate result)
I specified 2 conditions in the excel function above.
Condition 1 = If STATUS is ANI
Condition 2 = if USER ID appear ONCE(1)
I used * symbol for AND to apply both conditions. I would have use + for OR.
You can play with the formula by removing one condition, and you can also try changing the 1 to 2, or even 3 to see the behavior.
Does this work better for you ?