VBA Find highest number in a daily range

[iammike]

I have this sheet (which is generated by my Solar Inverter)

Which produces the following (2 columns)

date-time kWh-generated
for example

2022/11/09 17:00 13.29

in a 5 or 6 min interval for a whole week (this attached sheet is only a small part of it (total lines original sheet is over 2000))

Now I am looking for the highest number kWh on a day to day basis
(I marked it in green on the sheet)

BUT

it must ignore numbers from 7:00pm till 7:00am so only pick the highest number for that day in the period from 7am - 6:xxPM as Solar is producing from approx 7am onwards till 5-6pm only.

Is this possible with VBA?

TiA

[iammike]

What also would help if I can find the highest number (day per day) in the range let's say 7pm-8pm.

In that range (time frame) the highest number of the day should be known, but a problem is sometimes the inverter logs in a 5 min but the start time is different so maybe the range can be

2022/11/08 19:04
2022/11/08 19:09
2022/11/08 19:14
2022/11/08 19:19
2022/11/08 19:24
2022/11/08 19:29
2022/11/08 19:34
2022/11/08 19:39
2022/11/08 19:45

or

2022/11/09 19:03
2022/11/09 19:08
2022/11/09 19:14
2022/11/09 19:19
2022/11/09 19:24
2022/11/09 19:29
2022/11/09 19:34
2022/11/09 19:39
2022/11/09 19:44

So you see the times aren't the same on a day to day basis.

[iammike]

If not possible via VBA of course via a Formula or another way would be absolutely great as well

TiA

[jindon]

See if this is how you want it.

Please Login or Register  to view this content.

[iammike]

Almost perfect, 7th is correct.

but on the 8th, the highest is 10.93 but script says: 9.95

and on the 9th the highest is 13.29 but script produces 9.88

[iammike]

Solved it

changed time from 7am - 7pm to 7pm - 8pm, because then for sure the highest number is available

Thx a Bunch, really appreciated.

[jindon]

OK, try insert one line in bold

Please Login or Register  to view this content.

[iammike]

Yes works! Absolutely great.

[iammike]

It works perfectly, but Would it be very hard to adjust if for example the date/time is still in column A but the data (other data) I am looking for is in (for example Column Q?)

There are more columns in those particular sheet, the last column with interesting data is in Column BL

[alansidman]

an alternative to vba is power query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[iammike]

OOoh wow, that would do me as well because there are a LOT more columns (up to "BL") in that XLS.

Will this work for my version?? About Excel says: Version 2210 (build 15726.20202 Click to Run)

Yes it does

Excel 2016 or newer, where Power Query capabilities are integrated within the Data tab. More information

[jindon]

When you ask question with the sample workbook, it should follow the same data types/structure as your actual data,
in order to avoid unnecessary back and forth/guess work.

So you will need to upload a sample workbook again.

[iammike]

Getting an error:

Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1

Tried to rename to the name of the sheet but no go

[iammike]

@jindon.

My Bad sorry. Will do that of course.

it's just a BIG XLS lots and lots of data, for these couple of days it's over 1,5 MiB in size and lots of info in it I will never need.

Thx again, and sorry again

[alansidman]

Tried to rename to the name of the sheet but no go

Do not rename the sheet, you need to make sure the table you are loading to excel has the same name. Either change the name of your table or change the Mcode to the name of your table.

[iammike]

Do not rename the sheet, you need to make sure the table you are loading to excel has the same name. Either change the name of your table or change the Mcode to the name of your table.

Aah I need to convert the Data to a Table first

Did that and,

on the 8th, the highest is 10.93 but it comes up with 9.95
on the 9th the highest is 13.29 but it comes up with 9.88
etc

[alansidman]

My apologies. In the first attempt, I did not change the data type to decimal and left it as mixed type. Here is the new Mcode.

Please Login or Register  to view this content.

[iammike]

@jindon.

Here is the whole Sheet (only 3 days worth) with all the Info I am looking for (highest values)

I marked the columns in Green

[jindon]

try chnge to

Please Login or Register  to view this content.

[iammike]

WOW yes absolutely totally works

One more question please, would it be possible to add the (finished) data to the same worksheet every time? Now it creates a separate sheet every time it runs. No problem if it overwrites the data in that new sheet.

Thx a lot.

[jindon]

Code in post #19 has been edit as per your post #20.

[iammike]

Oooh Wow, I wish I could add more reputation.

Really really appreciated

And for my understanding

If I want more columns, I just add them to this list?

Please Login or Register  to view this content.

Correct?

Thx again.

[jindon]

Yep, correct, and you can even change the order EXCEPT Date/Time column i.e. 1.