Hi Elinor,

As I understand you want on your sheet 2 the information of the different Plate No's as presented, where the information comes from sheet 1.

That's not with the IF formula, but can (for instance) be done by the Vlookup formula.

Point is that your data on sheet1 needs some customizing before you can use that formula.

Another way is doing this with a pivot-table, but also for that the data needs customizing.

There might be other ways, but as I see it you still have to customize the data on sheet1.

So if another person here on the Forum knows another way, I'm glad to hear that. But otherwise we can show you how to customize the data and after that make a report with Vlookup or Pivottable if you want to.

Frans

Hi Elinor

You can refer to my example attached as a starting point. The formula is very complicated and crazy but it should work (provided your data is clean) .

I have intentionally left the formula to get the row number in cell K1 to show you what I am doing but you can merge this into the other formulas if you want. The formula will then be even more complicated and difficult to decipher.

NOTE : It is important that the serial number in column C of sheet1 must be in sequence else my formulas will not work. You will also have to ensure that all the values in columns D to I are filled in correctly. I have highlighted some errors in your data that will affect the results e.g Column C rows 35-38, 53-54.

I also noted that the Plate number is text so my formula in K1 is set to look for text. If you decide to change the plate to a real number, then you will need to change my formula. I have set the max rows to be extracted to 30 as I don't know what is the actual numbers. In your example the max rows is 26.

You will need to give it a thorough testing as it may not work in all situations.

Good luck.

Sunny

Hi Frans Visser and SunnyKow,

thank you very very much for solving my problem. I studied and tried your explanation SunnyKow and it worked. Thank you again and GOD BLESS!!!

Hi Elinor,

Although Sunny has provided you with a formula that will work, take heed of his warnings and consider them a reason not to use the formula solution. One thing Sunny didn't mention is the formula uses volatile functions, which can result in slow files if used too much.

You would be safer to use the Power Query solution I provided you via email. I've attached it here for your and others reference. It really is far easier than trying to work with a temperamental formula!

Mynda

Good day Ms Mynda,

Thank you very much for answering my problem on excel and i will study the information on Power Query you sent.

And also i want to thank you again, because of this blog i learned a lot especially excel and word.

GOD BLESS YOU AND YOUR TEAM!!!

You're most welcome, Elinor. It's great to know we can help.