Removal of Duplicates reading /invalid rows

Hi Excel Experts,
As per available readings data for different machines (Mch Code) , it consist of duplicates (more than 2 readings records per machines) for some machines. However, for analysis or efficiency calculation purpose, we want to keep it a single reading line per machine (Mch Code) basis following criteria...

1. Firstly , 1st Reading is to be matched

2. Secondly,
a. In case of similar 1st Reading in multiple rows for similar machine (Mch Code) with IDENTICAL 1st Date (date & time), it should be considered ANY one row.
b. In case of similar 1st Reading in multiple rows for similar machine (Mch Code) with DIFFERENT 1st Date (date & time), it should be considered a row with SMALLEST 1st Date (date & time)
c. In case of similar 1st Reading in single row for similar machine, same row is to be considered.
d. In case of 1st Reading is NOT MATCHING for specific machine, error message is to be appeared i.e. 1st Reading not appearing in (not matching with) available data.

Please ref. to attached file for details

At present we do this exercise manually (remembering above criterion), How to get this working with automatic formulas or VBA in Excel, help in this issue would be great! THANKS IN ADVANCE.

Ravi

I am not able to upload file. It is working.
Try this

Range R2:W2
Mch Code |1st Date |2nd Date| 1st Reading| 2nd Reading| Diff

ARRAY formula in R3, then drag down

=IFERROR(INDEX($A$3:$A$62,SMALL(IF(COUNTIF($R$2:$R2,$A$3:$A$62)=0,ROW($A$3:$A$62),""),1)-ROW($A$3)+1),"")

ARRAY formula in S3, then drag across

=IFERROR(INDEX($C$3:$G$62,SMALL(IF(IF(($A$3:$A$62=$R3),(VALUE($C$3:$C$62)),"")=SMALL(IF((($A$3:$A$62=$R3)*VALUE($C$3:$C$62)),VALUE($C$3:$C$62),""),1),ROW($A$3:$A$62),""),1)-ROW($C$3)+1,MATCH(S$2,$C$2:$G$2,0)),"")

Format cells as per requirement

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

Dear Sir,
Thanks for your reply; as per your instructions in reply, I have put formulae in R3 and S3 with pressing Ctrl+Shift+Enter keys together, however, it's not working.

If it is working at your end forward file with formulae.

Please revert

Sir,
Awaiting a great answer from you.

- Ravi

See attached based on kvsrinivasamurthy's reply.

Sir,

Output is not appearing after clicking on after clicking on ENABLE EDITING
(kindly ref. to attached snaps with ENABLE EDITING (before clicking) and without ENABLE EDITING (after clicking)

Please help.

Ravi

I have downloaded the file I posted and it worked fine.

I see the file is READ-ONLY: so save with new file name.

Sir,

saved as new file also, however, result is as same as mentioned in my previous mail ,( appearing out put is blank)

- Ravi

Don't understand why: As I haven't got Excel 2013 ..is there a setting needs changing??

What happens if you activate "Formulas" ==>"Show Formulas" ?

Copy/paste this into S3

=IFERROR(INDEX($C$3:$G$62,SMALL(IF(IF(($A$3:$A$62=$R3),(VALUE($C$3:$C$62)),"")=SMALL(IF((($A$3:$A$62=$R3)*VALUE($C$3:$C$62)),VALUE($C$3:$C$62),""),1),ROW($A$3:$A$62),""),1)-ROW($C$3)+1,MATCH(S$2,$C$2:$G$2,0)),"")

Enter with Ctrl+Shift+Enter

copy across and down

Not working

... I have no further ideas if you cannot simply enter the formula!

What happens if you activate "Formulas" ==>"Show Formulas" ?

Sir
Yes, it working now
Thanks a lot...only thing is each and every-time we will have to..
Copy and past values from columns X and Y column to columns C and D

Ravi

Select column C (row 3 down)

Text-to-Columns ==>skip to Step 3 ==>check "Date"==>Finish: data converted to date/time format

Repeat for D

Still do not understand why it work as original where C & D are formatted as general.

Sir,
Is there any way to make it in VBA, if issue is with formula.
- Ravi

Can any one help me to resolve it. Thank you for your help in advance
- Ravi

Can any one help me to resolve it. Thank you for your help in advance
- Ravi

Any update on this

Have you tried my suggestion in #25 yet?

Looking back at the two formula by @kvsrinivasamurthy in post #2, the first one shouldn't need to be changed, as it doesn't refer to columns C or D.

The second formula refers to ranges in columns C to G. I don't know if these can be split, so it may be easier to replicate the values of columns E to G in columns Z to AB. Enter the following formula, and copy down:

• In cell Z1, use formula =E1
• In cell AA1, use formula =F1
• In cell AB1, use formula =G1

Copy these three formulae down.

The second array formula should then be:

=IFERROR(INDEX($X$3:$AB$62,SMALL(IF(IF(($A$3:$A$62=$R3),(VALUE($X$3:$X$62)),"")=SMALL(IF((($A$3:$A$62=$R3)*VALUE($X$3:$X$62)),VALUE($X$3:$X$62),""),1),ROW($A$3:$A$62),""),1)-ROW($X$3)+1,MATCH(S$2,$X$2:$AB$2,0)),"")

Remember to enter/copy the array formulae as described in post #2.

Sir,
I tried as per your instructions, but it's not working, can you please try the same at your end and revert with attachment, if it's working.
- Ravi

I have tried, but its not working for me either. The original formula did work for me though - presumably due to different date settings?

Unfortunately, I do not understand the array formula. I simply noticed that it referred to a range covering columns C-G, so re-wrote it amending the column references to the new columns created. But perhaps there's something else that needs to be done to the array formula.

Perhaps someone with a better understanding of array formulas will see this and be able to advise.

The other possibility is could you check (and possibly change) your date settings? It seems to be working fine for some posters to this thread, but not for others. I've taken a screen shot of my format date screen, in case this gives any help - if there are any other settings that it may be useful to compare, let me know. I've also attached a copy of the file with my amendments to it, in case this is of use to anyone that can help with the array formulae.

Sorry that its not a solution, but hopefully its a step closer!

Trevor


(Using Excel 2007 on Windows 8.1)

Sir,

For me appearing out put is blank (like Post# IMG with blank output)

- Ravi

Same for me, after my changes. I uploaded it as I thought that it may help someone who understands array formulae better than me see what is wrong. I had thought that it would just be a case of taking the formula from the earlier post and amending the column references, but it seems that there must be more to it than that.

However,the earlier version of the spreadsheet (before adding columns X:AB) did work for me. I'm still convinced that the reason that it doesn't for you and some other posters is due to date settings. This is why I added the screen picture, so that you would be able to see if settings differed.

Can any one help me to resolve it. Thank you for your help in advance
- Ravi

Can any one help me to resolve it. Thank you for your help in advance
- Ravi

Please post your latest file, explaining outstanding problem.

Not working

Values are appearing before clicking on ENABLE_EDITING,
After clicking on ENABLE_EDITING the values are disappearing

Please ref. to attached images for details.

- Ravi

Sir,
It's working , but can you extend 300 (A3:G300) rows instead of 62 as data can be flexible/variable within 300 rows..

I tried but it's not working,please ref. attached file for the same

- Ravi

Please help me to resolve it. Thank you for your help in advance
- Ravi

Just use "Find and Replace"

Find: $A$62

Replace: $A$300

Replace All

As instructed ..
Replaced all $A$62 with $A$300

However, it's not working (result appearing blank)

Both the changes done, but it's not working (result same ; appearing blank) and diff is #VALUE!
(ref. attached file with said changes)

Thanks a lot sir,

It Working for me also.
- Ravi