Analyzing Excel Report

[Basavaraj093]

Hi all,

I am working on a LabVIEW project where I need to retrieve and combine data from multiple Excel sheets based on a selected date range. Each sheet's name corresponds to a date (e.g., "01-01-2025", "02-01-2025"). I can already get the sheet names and the required data from individual sheets using ActiveX.

Thanks to Someone who posted it in forum.

Requirement:

Filter sheets based on a date range (e.g., from "02-01-2025" to "04-01-2025").

Combine specific data (e.g., quantity) from the filtered sheets into a single total.

Example:

For sheets with the following data:

01-01-2025: Qty = 10

02-01-2025: Qty = 4

03-01-2025: Qty = 7

04-01-2025: Qty = 30

05-01-2025: Qty = 35

If I select the date range from 02-01-2025 to 04-01-2025, the total quantity should be 41 (4 + 7 + 30).

How can I efficiently filter the sheets by date range, retrieve the quantity data from each filtered sheet, and calculate the total in LabVIEW?

I have attached what I have done, The Excel is different, I can't share original one, but the report looks like this only.

[Bob_Schor]

Consider your data format:  Date in the form "01-01-2025" (which I assume is a string) and "Qty = 40" (which I assume is another string).  I'm assuming that you have two Excel columns to read, one of Data strings, one of Quantity strings.

I would make a Cluster with two elements -- Sortable Date (an I32) and Quantity (a Dbl, or an I32 if always an integer).  You need a function "Make Sortable Date" that changes your "Date" (01-02-2025) which is difficult to sort and compare into "Sortable Data" (20250102) which is (Day #) + 100*(Month #) + 10000*(Year #).  All you now need is to translate Date into Sortable Data -- fortunately, Scan from String (look in String Palette) can recognize 2-digit integer, "-", 2-digit integer, "-", 4-digit Integer into "Month #", "Day #", "Year #".  Similarly, the string "Qty - 40" can be turned into an integer "Quantity".

Read your sheet data and build an Array of Clusters.  Sort the Array -- it will sort on the first element, "Sortable Date".  Now your task almost solves itself -- you can enter a Data Range, change the Dates to Sortable Dates, the process the "sorted" Array of Clusters.  Start accumulating Quantity when you find the Array you just constructed looking for a Sortable Date >= First Sortable Date, and stop when Sortable Date > Last Sortable Date.  Do yourself a favor and end the Array with an entry with Sortable Date of "99999999".

Bob Schor

[Basavaraj093]

Thank you for your reply,

The Sheets names are Sorted in the excel itself. 

If I have total 5 sheets and only want from 3 Using Date, I can get required Sheets.

and I tried your method also

  I am Struck After This how to get those sheet data. In different arrays.