Analysing variation of status through time from different tables - Line chart

excelsa

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, I am having a brain freeze issue because I cannot think of a solution to the below - I am sure you guys will come up with something that will be obvious once it's explained to me lol.

So basically I have different sources of data, all containing the same format (i.e. column names) but each source is an updated version which contain the new status of the item. For example:

Table1
Item NameStatusDate
Item0Not Verified01/07/2020
Item1Not Verified01/07/2020
Item2Not Verified01/07/2020
Item3Not Verified01/07/2020
Item4Not Verified01/07/2020

Table2
Item NameStatusDate
Item0Verified01/08/2020
Item1Not Verified01/08/2020
Item2Verified01/08/2020
Item3Not Verified01/08/2020
Item4Not Verified01/08/2020

Table3
Item NameStatusDate
Item0Verified01/09/2020
Item1Verified01/09/2020
Item2Verified01/09/2020
Item3Verified01/09/2020
Item4Verified01/09/2020

I need to somehow get this data together (preferably using data model relationships) and come up with a line chart that would show the number of items per date per status. A bit like the example below:

Status Chart (1).jpg

If you could explain how to join/relate the data, which fields for the chart (do we need to create helper fields?), etc... I would be very very grateful.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,571
Office Version
  1. 2010
Platform
  1. Windows
I think you just need a row of countif statements like this:

=COUNTIF(Table1[[#All],[Column1]],"Verified")=COUNTIF(Table2[[#All],[Column1]],"Verified")=COUNTIF(Table3[[#All],[Column1]],"Verified")
 

excelsa

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I think you just need a row of countif statements like this:

=COUNTIF(Table1[[#All],[Column1]],"Verified")=COUNTIF(Table2[[#All],[Column1]],"Verified")=COUNTIF(Table3[[#All],[Column1]],"Verified")

Hi @offthelip thanks for the reply but I don't think that is the solution I was looking for - although your solution could work for fixed data. I need this to be dynamic so every time a new table (with new status) is added I wouldn't need to update to the formula. I was looking for something using data relationship: is it best to join or can I use relationships between the tables; should the columns be pivoted (which ones); etc.
 

excelsa

New Member
Joined
Sep 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all, just wanted to mark this post as finished. I think the only way of achieving what I want is by 'appending' the files - which can be achieved by using Power Query for example.

Sorry moderators for not finding the way of closing this thread (I honestly can't find how to close it myself)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,996
Messages
5,575,433
Members
412,664
Latest member
akilah03
Top