I have a table of total 12 columns and 30 rows. The table looks like below. Note that real data are very different than this, but follows this pattern - the value goes upto some number and keeps repeating for all rows.

But I am getting this-

I am able to get an expected chart by manually deleting repeating values from the table. I am looking for a way to do that automatically.

CodePudding user response：

Assuming your date is like this:

You could create another table referring to the first one with these formulas:

So basically, this formula =IF(B3-B2=0,NA(),B3) in H4 copy-pasted in all cells but the first row.

Which would give:

And plotting this second table would give you the desired result since NAs aren't plotted (as mentionned by Solar Mike).

Caveat

This works only if the values are stricly increasing or decreasing for every row. If there is no change between 2 data points before the end of the series where it flattens for good, then there would be missing point in your line.

For example, if 2020.Q2 started with two zeroes in a row, you would have a NA appear before you want it.

So, you would still need to manually replace those NAs.

But if you want to automate the whole process, you could add another table that checks if there is non-NA values after a NA and if there is change it back to the previous number.

Something like this:

In this solution, the formula in O3 would have to be : =IF(AND(ISNA(I3),PRODUCT(IF(NOT(ISNA(I4:I$8)),0,1))=0),I2,I3)

Explanation:

• I4:I$8 is the range of values after the current cell. We use the $ so that the range is anchored to the last row.
• IF(NOT(ISNA(I4:I$8)),0,1) returns an array filled with 0's where there is a non-NA value and 1's when the value is NA.
• PRODUCT(IF(NOT(ISNA(I4:I$8)),0,1))=0 checks if the product of the elements in the array is 0. Since only one zero is needed for the value of the product to be zero, this essentially checks if there at least one non-NA value after the current one.

CodePudding user response：

Replace the zeroes.

Use na()

As that is not plotted.

See:

Added benefit: if overlap is set to 100% on the blue series then it looks like the value is "highlighted" in the first series - when discussing data it is a neat way to focus attention.

Edit, this works whether the preceeding values increase or decrease:

CodePudding user response：

1-Select a column (or columns) to look for duplicated data.

2-Open the Data tab at the top of the ribbon.

3-Find the Data Tools menu, and click Remove Duplicates.

4-Press the OK button

Is this that you want?

CodePudding user response：

So to solve my problem, I created one more table and applied the formula =IF(MAX(B$1:B$8)=H1,NA(),B2). This formula computes the maximum value of the source table column and compares it with the upper immediate value. New table looks like this-

Range of this table is from G1:K8