Comparing and mark data from two different worksheet

[pyol17]

Hello guys. I'm new and i wanna ask experts here how to solve my problem.


I have two worksheet and each contains series of Serial Number and Operation Number. So how can the first worksheet ( will be marked) can be compared with the second worksheet ( reference ) if it has the same Serial Number and Operation number ( fullfill both conditions) . If the first worksheet contains redundant data, the third row will be marked and indicated, let say with "submitted" .

I have read about vlookup and if function, but i'm not doing so well or maybe they doesnt suit this kind of problem .


Thanks in advance for helping.

[JBeaucaire]

Put this in C5 and copy down. Expand the ranges as needed. Do not try to use full columns, just the ranges needed:

=IF(ISNUMBER(MATCH(A5&"-"&B5, INDEX(Sheet2!$A$6:$A$14&"-"&Sheet2!$B$6:$B$14, 0), 0)), "Submitted", "")

[pyol17]

ok thank you. Thats work impressively for me..

But is it possible to be done automatically? I mean everyday i need to update both sheet1 and 2 with new serial and operation number, which involve hundreds of rows per week need to be top-up. so what if the comparison can be done without a need to drag down the formula in c5.

[MarvinP]

Hi pyol17,

Watch this video. http://www.mrexcel.com/Excel_Double_...ing.html#video

[pyol17]

this video is about my first question in the tittle or the latter add-on one... ?

because it took quite time to fully buffer the video. and i dont bring my earphone. in the office right now.

[pyol17]

ok thanks marvin..

watched already.

but this double click only works downwards only right? what if i need to top up every serial and operation number on the top of the list so it will be sorted based on its modified date.Does this method works as well?

Plus,i need to adjust the range every time new data kept in. i can extend them up to 10000 rows but what if it exceeds the limit without i realize. Even 10000000 can wok i guess.


=IF(ISNUMBER(MATCH(A5&"-"&B5, INDEX(Sheet2!$A$6:$A$14&"-"&Sheet2!$B$6:$B$14, 0), 0)), "Submitted", "")

so, do you think any way better?

[JBeaucaire]

Here's a little macro to input the formula for you on SHeet1 in the range needed, expanding the reference range on sheet2 as needed:

Please Login or Register  to view this content.

[pyol17]

honestly speak, i never use macro before.but after some googling, i paste ur coding into visual basic and run it. even that i'm not sure doest those code works or not.

Then i tried to insert more than 200 rows of serial number to be tested.

so even the formula range only =IF(ISNUMBER(MATCH($A211&"-"&$B211, INDEX(Sheet2!$A$6:$A$31&"-"&Sheet2!$B$6:$B$31, 0), 0)), "Submitted", "")
, it works entirely for new data entry, exceeding 200 rows.


i guess this problem is totally solved. Thank u JBeaucaire for ur help.

[JBeaucaire]

Well, it's not true the formula would work if the 200 rows was on Sheet2, but if the 200 rows is on sheet1 and the range on sheet2 is still just A6:B31, yes, the original formula would work.


If the range of reference data on sheet2 expands, the macro would edit the formula for you.

Glad it's working for you.

[pyol17]

prototaip.xlsx

i have used ur formula , but i insert it on L18 in ''checklist'' sheet. The reference sheet2 i renamed it become ''submitted'' .And few changes in term of position only.


The formula works fine, but i only need to adjust the range.


So can you help me to alter the macro part to fit my new changes.Really apreciate ur help dude.

[JBeaucaire]

Please Login or Register  to view this content.

The edits are in red, with the original code and now this edited version, you should be able to adapt your own edits in the future.