Identify discrepancy

[OCM]

Hi,
I receive three monthly reports which I expect to see the values for the 'completed date' field should be consistent/the same. However, every now and then for the same person, the 'completed date' is not consistent and I would like to filter out these records so I can reach out back to the source to make correction. All three 'completed date' values should be the same. If not, I'd like to filter out those records

Basically in the attached report my goal is to find a way to filter out the one with an *
This is because one or more 'completed date' for the same person is not the same.

Any ideas?

TIA

Regards,



OCM

 

[SkipVought]

Hi,

First off, do you have to have the "n/a" TEXT in the numeric Date column?

My solution: Notice I deleted the row containing the "n/a"s
Also I made your table a Structured Table that is simpler to analyze, using your table headings.

And if the count could be something other than 3, then this is a better formula...
=IF(SUMPRODUCT(([ID'#]=[@[ID'#]])*([Completed date]=[@[Completed date]]))<>COUNTIF([ID'#],[@[ID'#]]),"*","")

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

You "receive three monthly reports" from 3 different sources / data bases?
Or is it the same source/DB, since you say you "can reach out back to the [one?] source to make correction"?
If that's the case: 3 different 'Completed date' for the same 'ID#'/'Last Name'/'First Initial' from the same, one source, then your 'source' is set up wrong allowing 3 different 'Completed date' where there should be just 1.

Or am I missing something here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[OCM]

Hi,
Thanks for your response
Skip No, I do not. I put n/a to show a given record didn't appear in one of the three data sources (in this example, source #3).
I tried the formula

 = [@[ID'#]])*( [Completed date] = [@[Completed date]]  ))<>3,"*","")]

and received an error:

Can you tell me how you made it structured table?

Andrzejek:
I received the report from three different sources. Ideally, all three should show the same 'Completed date'. But, due to data input error, sometimes I notice inconsistency.
The goal here is if all three 'Completed date' are the same, this is the expected result. What I'm trying to identify is when one or two of the 'Completed date' value is different. In other words, when the 'Completed date' values are not the same.

Hope this explains,

TIA
Regards,

OCM

 

[Andrzejek]

For this task, you may benefit from Power Query in Excel

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Structured Tables

https://support.microsoft.com/en-us...l-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Rather than using anonymous A1 references, actual descriptive names can be used. The entire table is treated as an entity as well as parts of the table like Fields or specific rows based on data in that row.

Once you have converted a table via Insert > Tables > Table... you can change the Name of your table and fill a formula in the Formula Bar, by simply pointing your cursor to the column or cell, and the correct reference will appear in your formula.

As rows are added or deleted, your formulas will automatically be in sync with the actual data.

BTW, the error was a result of the fact that your table needs to be converted to a Structured Table with the proper table name for my formula to function.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[OCM]

Okay, I'll give it a try and post back if I have additional questions. I would also like to try this in Power Query, but I am not familiar with Power Query. If you can point me to a Power Query community forum and/or resources will be great.


TIA

Regards,

OCM

 

[combo]

I would also like to try this in Power Query

Before starting with Power Query (PQ) you need to start to work with structured tables. When you have a table in Excel, you have to Insert>Table having active cell inside, and Excel will create default table. You can format it, extend and use structured formulas, add summary row, etc.
What is most important, PQ can see only structured tables in case of working with data inside Excel file.

PQ is a powerful com add-in to transform data, available in Excel 2016+, MS PowerBI (or especially in PowerBI). It has its own M language for processing data, files, defining functions, query flow, etc. PQ has its own desktop to work.
Generally, PQ query is a set of transformations and calculations. One Excel file can have several queries embedded, all available in PQ environment.
A query can have an output to worksheet or stay as connection only.

To start with PQ, go to (Excel 2016) Data tab and select 'from table'. In the 'new query' drop-down you can see a range of sources accessible by PQ.
If you know the rules, the start is easy, PQ records your actions in the query. They may be modified in command line or advanced editor, depending on user's skills.

I enclose your file with PQ queries. I added:
1. structured table 'tSample' from your source data,
2. 'tSample' query to pick the source in PQ,
3. 'qNotConsistent' query, that tries to extract non-consistent IDS, it groups and counts similar records, removes data if count=3, extracts unique IDs. 'n/a' makes some mess here as the last column is a mixture of text and dates. Moreover, a weak point is that I assumed that only count=3 is ok - it may not be true and other approach may be required. I removed 'n/a', so it is not in output too,
4. 'qOutput' merges the two queries in exact join of IDs and outputs to workbook.

All you need in case of change of input data is to right-click the output and refresh.
Aggregating rows for counting is case sensitive here, so 'P' and 'p' (row 4) are recognised as different.

combo

 

[OCM]

Combo,
Thanks for the reply post and lead me to PQ. It looks very powerful and I've a lot to learn.
The sample you show was the expected result I was looking for.
1. Structure table: To make my table a structure table, all I did was I highlighted the data Insert > Table
2. PQ: to start, I sorted my data (screenshot)

My excel is consists of 3000+ rows. How can I tell PQ what the next step is, i.e., filter out those inconsistent records?

I'm using Office 365.

TIA

Regards,

OCM

 

[combo]

I used helper query first to identify IDs that are (or are not) consistent. This query was next merged with input data to exclude (or include) data. So I needed three queries:
- first: simple input,
- second: identify IDs: I grouped, counted, filtered records, for me you need it first,
- final third: merge first and second, filter, output to worksheet.

If you like to follow this way:
1. Stop the first query here. Close the PQ from menu and create connection only. I can see that you left the default name of structured table (Table1), PQ created the query with the same name. It will be used as input for selection of IDs and final queries.
2. In PQ or Excel create new query that will extract not consistent IDs: new query>from other sources>blank query. In PQ start the query in command line with '=Table1'. It is the input in new query. To identify not consistent IDs I aggregated and counted records, you can see it in my file.

If you like to use and analyse my solution, copy your data to my workbook (if the structure is the same) and refresh output data.

You can also copy my solution to your workbook:
- rename your structured table as the tamle in my workbook ('tSample'),
- copy queries from my workbook and paste to yours.

When you select a query, select step by step steps actions in 'Applied steps' pane, you can see the consecutive data transformations in query. Some actions have little cogwheel on the right, when you click it, you can configure the step.

I'm not sure if this manual is precise PQ in Excel 365 description, but can be helpful in discovery of PQ possibilities:

https://learn.microsoft.com/en-us/power-query/power-query-ui

combo

 

[OCM]

combo
Thank you for your feedback and for the PQ resource link, I appreciate it. I’ll try your suggestions when I get back to work.

Regards,


OCM

 

[Andrzejek]

You know, your life would be a lot easier if you would drop these 'three monthly reports' into a DB table (Access?) instead of Excel.
You could just do something like this:[TT]

Select ID, Count([Completed date]) As MyCount
From YourTable
Group By ID
Having Count([Completed date]) > 1[/TT]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson