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

#### excelsa

##### New Member
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:

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.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### offthelip

##### Well-known Member
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
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
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)

Replies
2
Views
206
Replies
5
Views
4K
Replies
3
Views
1K
Replies
3
Views
67
Replies
1
Views
65

1,171,628
Messages
5,876,529
Members
433,199
Latest member
guerin47

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back