How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6204
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I want to compare three columns in one row (day, month and

This answer was rated:

I want to compare three columns in one row (day, month and yr) with the three cells below and if the same (i.e. the same day, mth and yr) compare the values in the fourth column for the same two rows and return the greatest value in that fourth column?

Hello and thank you for your question today. My name is Jason. I look forward to helping you today.

May I ask for you to give me three examples of what you are referring to?


I ask for three examples just to be sure that I get it right for you.

Thank you.


Customer: replied 3 years ago.

In excelI want to compare three columns in one row (day, month and yr) with the three cells below and if the same (i.e. the same day, mth and yr) compare the values in the fourth column for the same two rows and return the greatest value in that fourth column?

Thank you.

I see that you pasted the question.

Do you have an example worksheet that you can send me so I can see exactly what you need?

Will this be done in multiple areas of the sheet or just the one?




Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.

this is an example that ok? I want to put in the last column the highest FDI for a day


 








































































DayMonthYearFDIHighest FDI on a day
11195710
2119579
21195715
3119579
31195715
4119579
41195715
5119579
51195715
6119579
Thank you.

You can use the following formula:

=IF(AND(A2=A3,B2=B3,C2=C3),IF(D2>=D3,D2,D3),"")

I have attached an example that shows this in action.

Here is the example: http://wikisend.com/download/447058/if.xls

Customer: replied 3 years ago.

Thanks its great if there are always two values for one day but sometimes there are more or less than that i.e. sometimes there will be more rows (FDIs) for the same date or only one entry - I want to end up with a list of dates and the highest FDI value for that date?



Okay, that is new information. What is the maximum amount of rows you wish for it to compare?

Also, will the rows be in order as such?
Customer: replied 3 years ago.

Yep sorry, no. of rows varies - sometimes several or many records for one day i think not more than 48 (one every half hr), sometimes only two.


 


So i'm trying to extract from the data set the highest FDI for each day



Okay, thank you for the update.

If I had been given all of the information, I would have been able to see that this is in the wrong category.

You need a programming answer.

I will have the question placed in the correct category.

When a programmer picks up the question, you will be notified.
Customer: replied 3 years ago.

thank you Jason.

Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Can we just created a NEW column with rows containing Days 1 to 31 and then reflect the highest FDI in a particular day? This will allow you to really pick the highest FDI regardless of the number of rows with same day.


Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 3 years ago.

Hi yes a new column fine, but I have a lot of data (over 20yrs) with days, months and year?


 


 

Hi,

Thank you for writing back. I need to know exactly HOW your data is organized. You posted something like below:

DayMonthYearFDIHighest FDI on a day
11195710
2119579
21195715
3119579
31195715
4119579
41195715
5119579
51195715
6119579

Now, in your actual data there, are they organized by YEAR? Like 1998 is on a separate sheet, 1999 on separate sheet, and so on? Or are they mixed up regardless of the year?

Jess
Customer: replied 3 years ago.

Hello Jess the data is on one excel worksheet all in separate columns as above.


 


could send workbook with the one worksheet and four columns of data if that helps.


 


 

Yes, please. Use http://wikisend.com and upload the Excel file there. Then gove me the download link or the 6-digit File ID so that I can download your file.

Jess
Customer: replied 3 years ago.

wikisend not working for me can i email it direct somehow, its only few Kbs

Customer: replied 3 years ago.

Ok here it is file id 575762


 


link


http://wikisend.com/download/575762/Benalls Shadford St data.xlsx

Thank you. I am downloading your file and will work on it. It may take some time since the task is tricky. I will get back to you with some feedback when I am done.

Jess
Hi,

After some time of trying to do the tasks that you require, I was able to solve the problem using pivot tables.

In this case, the MAX or the highest FFDI value in every DAY is reported and this is done in every month and in every year.

Here is the download link:
http://wikisend.com/download/292736/Benalls_Shadford_St_data_rev1.xlsx

In that first file, the highest FFDI is calculated all days, all months in all years.

In this second file below, I generated a different layout but I decided to extract all reports PER YEAR. This is just an option depending on how this can help your actual work.

File 2 below:
http://wikisend.com/download/594228/Benalls_Shadford_St_data_rev2.xlsx

I hope this helped. If you need assistance in the future, you can request me any time. Or you can simply post a new question saying "For jessmagz" so that I can assist you as soon as possible.

Please remember to rate my service positively (3-5 stars/faces) once you have all the
information you need. Tips are always highly appreciated! Cool

If you have any other questions,please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6204
Experience: Computer Software Specialist for more than 10 years
Jess M. and other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.

Thanks Jess looks great, excellent work and rated accordingly :)