User Profile
marshalltj67
Brass Contributor
Joined Nov 04, 2023
User Widgets
Recent Discussions
Pivot Table Grouping
Good Afternoon All, I currently have two pivot tables, one has solar energy production data for a day split in hours, i.e., "Monday, 7/01/2024 - 12:00am - 1:00am" and the other has electricity usage data for a day split in 15 minute increments, i.e., "2024-07-01 00:00:00 to 2024-07-01 00:15:00", "2024-07-01 00:15:00 to 2024-07-01 00:30:00", and so on and so fourth. I am trying to group the pivot table that is split in 15 minute increments by every 4 rows to match the solar energy production data for one hour increments but I am having trouble working the pivot table to group automatically. I tried creating a calculated field using the following formula but had no luck: SUM(OFFSET("STARTING ROW",(ROW()-ROW("COLUMN START"))*4,0,4,1)) Is there an easier way to compute this so when I update the data it is automatically group by every 4 rows? It is a bunch of data (goes from 0000 to 2345 in 15 minutes) so grouping manually is not ideal. Please see the attached picture for reference and I can provide the excel sheet if needed. Thank you! Marshall664Views0likes2CommentsRe: Data Sheet Compare Macro
NikolinoDE After doing some more research, I found the error is fixed when performing a redundant double transposition. I do not know if this changes the function of that line but I think that error was due to it not inputting a 1 dim array into the "Source Array" for the Join("Source Array" , Delimiter: "|"). Please let me know if this affects the function of the code since I am now seeing issues with the output format. Please see the following: Sub CompareTwoTables() Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject Dim rngCell As Range, rowToday As ListRow, rowYesterday As ListRow Dim foundRow As Boolean ' Reference Tables Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData") Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData") ' Loop 1 - For Each rngCell In TodayDataTable.DataBodyRange If rngCell.Value <> YesterdayDataTable.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then rngCell.Interior.Color = vbYellow rngCell.Font.Color = vbRed End If Next rngCell ' Loop 2 - For Each rowToday In TodayDataTable.ListRows foundRow = False For Each rowYesterday In YesterdayDataTable.ListRows If Join((Application.Transpose(Application.Transpose(rowToday.Range.Value))), "|") = Join((Application.Transpose(Application.Transpose(rowYesterday.Range.Value))), "|") Then foundRow = True Exit For End If Next rowYesterday If Not foundRow Then rowToday.Range.Font.Color = vbGreen rowToday.Range.Interior.Color = vbYellow End If Next rowToday End Sub The output of the second loop seems to overwrite the first loop formatting on the "Today Data Table" as it highlights all cells yellow and changes all font to green (See attached - "Table Error (All Values Overwrited by Loop 2)". The following code works for the first loops as intended but still gets overwrited by the second loop (See attached - "Loop 1 - Correct Format"): ' Loop 1 - For Each rngCell In TodayDataTable.DataBodyRange If rngCell.Value <> YesterdayDataTable.Range(rngCell.Row, rngCell.Column).Value Then TodayDataTable.Range(rngCell.Row, rngCell.Column).Interior.Color = vbYellow TodayDataTable.Range(rngCell.Row, rngCell.Column).EntireRow.Font.Color = vbRed End If Next rngCell End Sub I know you said your answers are voluntary and not guaranteed so I appreciate any assistance you can provide.. I think the main function is there I am just wondering if you had any guidance on any additional small kinks to the code. Thanks again man! Marshall2.8KViews0likes1CommentRe: Data Sheet Compare Macro
NikolinoDE Is there another way to accomplish the same function the following line of code performs: If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then I keep getting an Invalid procedure call or argument (Error 5) and I am unaware if this is platform specific (I have tested on both Mac and Windows). From my research it seems like fairly common way to join and transpose arrays so I am unsure why it is reporting as an invalid procedure. Everything else works as expected from my test runs so I think this is the last piece. Again thank you so much for the support! Marshall2.9KViews0likes0CommentsRe: Data Sheet Compare Macro
NikolinoDE Note. I changed the "set" table to "YesterdayDataTable" and "TodayDataTable" to avoid confusion: I was able to figure out the first loop with this code: For Each rngCell In TodayDataTable.DataBodyRange If rngCell.Value <> YesterdayDataTable.Range(rngCell.row, rngCell.Column).Value Then TodayDataTable.Range(rngCell.row, rngCell.Column).Interior.Color = vbYellow TodayDataTable.Range(rngCell.row, rngCell.Column).EntireRow.Font.Color = vbRed End If Next rngCell I am still having trouble with the second loop for finding if a row as been added. Was rowYesterday supposed to be in the defined as a range along with rowToday? Here is what I have as a whole and I keep getting the attached error for "Compile Error - Argument Not Optional". I think the compile error is happening in the following code: "If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then" Thank you! Sub CompareTwoTables() Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject Dim rngCell As Range, rowToday As Range, rowYesterday As Range Dim foundRow As Boolean ' Reference Tables Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData") Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData") ' Loop 1 - For Each rngCell In TodayDataTable.DataBodyRange If rngCell.Value <> YesterdayDataTable.Range(rngCell.row, rngCell.Column).Value Then TodayDataTable.Range(rngCell.row, rngCell.Column).Interior.Color = vbYellow TodayDataTable.Range(rngCell.row, rngCell.Column).EntireRow.Font.Color = vbRed End If Next rngCell ' Loop 2 - ' Loop through each row in the "Today Data" table For Each rowToday In TodayDataTable.ListRows foundRow = False ' Check if the row exists in the "Yesterday Data" table For Each rowYesterday In YesterdayDataTable.ListRows If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then foundRow = True Exit For End If Next rowYesterday ' If the row doesn't exist in the "Yesterday Data" table, it's a new row If Not foundRow Then ' Highlight the entire row in yellow rowToday.Range.Interior.Color = vbYellow ' Format font color to green rowToday.Range.Font.Color = vbGreen End If Next rowToday End Sub2.9KViews0likes4CommentsRe: Data Sheet Compare Macro
For the first loop I am getting an "Run-time error '438' - Object doesn't support this property or method" error and for the second loop I am getting a "Compile Error: Argument not optional" error Does this have to do with how the initial Dim code is written for setting the initial ranges?3KViews0likes0CommentsRe: Data Sheet Compare Macro
NikolinoDE Please see the attached photos for what I am trying to accomplish with the Macro. Basically, I have a table that will always stay unformatted since it is the "Yesterday Data" since I want to highlight any changes. Once I run the query, the "Today Data" table will shift to a "Yesterday Data" sheet as a table and the query will pull the new data and place it in a "Today Data" sheet as a table. At that point, I will need to highlight if there are any changes in the following formats: New row added to the table: Highlight the entire row "Yellow" and format text as "Green" Update to data: Highlight the specific cell that changed "Yellow" and format the entire row text as "Red" I hope this helps! I tried running your code and it worked fine but not for this comparison application as I need to visually analyze the changed or newly added data on one sheet. Here is my updates to the code... I am trying to figure out how to perform a FOR IF loop for if a new row is added but I can't find anything on how to do it. Sub CompareTwoTables() Dim tbl1 As ListObject, tbl2 As ListObject Dim rngCell As Range ' Set references to the tables Set tbl1 = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData") Set tbl2 = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData") ' Loop through each cell in the "Yesterday Data" table For Each rngCell In tbl2.DataBodyRange ' Compare cell values between tables If rngCell.Value <> tbl1.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then ' Highlights cell that is differnt "Yellow" tbl2.Range(selCell.Address).Interior.Color = vbYellow ' Change font color to red tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Font.Color = vbRed End If ' Loop for if a new row is added For Each Rng In CellIntbl2.DataBodyRange If tbl2.ListRows(rngCell.Row - tbl1.HeaderRowRange.Row + 1).Range.Interior.Color = vbYellow tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Font.Color = vbGreen End If Next rngCell End Sub Thank you so much! I greatly appreciate the help! Marshall3KViews0likes7CommentsData Sheet Compare Macro
Good Afternoon All, I am very new to Macros and VBA and I am currently writing my first Macro to compare two data sheets with a specific format. I currently have two sheets with data outputted as a tables; this was done because the data updates daily so I have a query to reference a "yesterday's data" excel workbook file and a query to reference a "today's data" excel workbook file so all the data is in the same workbook in different sheets. These tables are then referenced to two separate sheets to get data out of the table format for the Macro to work. I currently have the Macro code to compare the two sheets and highlight the cell red if it has changed. Please see the attached excel workbook and the following code: Sub CompareTwoSheets() Dim dataRng As Range, selCell As Range Worksheets(1).Activate Set dataRng = ActiveCell.CurrentRegion For Each selCell In dataRng If selCell.Value <> Worksheets(2).Range(selCell.Address).Value Then Worksheets(2).Range(selCell.Address).Interior.Color = vbRed End If Next selCell End Sub 3 Questions: 1) Is there a way I can write this to reference the tables instead of the separate excel sheets? 2) Is there a way I can configure the format so instead of it highlighting the changed cell red, it changes the entire row to "Font Text - Red" and then highlights the change in "Yellow"? 3) Is there a way I can add a format so if a new row is added it changes the entire row to "Font Text - Green" I am running Microsoft Office Professional Plus 2016 on an offline system and do not have the capability to upgrade - Queries were developed on the offline system (attached workbook is a reference) I greatly appreciate the support - thank you! Marshall4.3KViews0likes9CommentsRe: How to perform excel data query with multiple changing conditions
I understand! I am working this code on a different stand alone system so it is hard paint the entire picture. It looks like when I run a query and the output updates my columns, my assigned names in the name manager update and if a new column is added, that column remains "Column #" so all I have to do is assign a new name so I should be good to go! I appreciate the help and my apologies for the delay!2.8KViews0likes1CommentPlus or Minus (+/-) Difference Formula
Good Afternoon All, I am trying to generate a formula that will give me the closest airport plus or minus (+/-) based off latitude and longitude. For example, I have an assigned location in a specific state with an assigned latitude and longitude value and I want to obtain the closest airport based off the comparison between the airport latitude and longitude values and the assigned location. I think 1 to 2 degrees of lat and long is suffice since 1 deg is ~ 69 miles. Please see the attached workbook for reference. Thanks!653Views0likes1CommentRe: How to perform excel data query with multiple changing conditions
Hi Lorenzo , By name manager I meant "Define Name" source that you get to by going to "Formulas" then "Name Manager". I was able to name all the columns via this name manager function by selecting all the columns, then clicking "Create from Selection" and then naming the column off the top row. This should be fine as long as all the columns remain the same. I am not sure if the assigned "name" for the column will update in the "Name Manger" if the query outputs another column. Thanks!2.8KViews0likes3CommentsRe: How to perform excel data query with multiple changing conditions
That worked perfectly! Thank you! I think I am almost there with the final query... Do you know if it is possible to name all the columns utilizing the name manager? For example if an output column from my query is "Tasking Date" is there a function I can use to save that column (A:A) as "Tasking Date"? I was able to get my script to query all the data and then I promoted the headers and then used Text.Trim to clean up the column names and data within the columns but I will be referencing the data in another sheet so I wanted to be able to reference "Tasking Date" as a named column just in case the column gets shifted. Thanks! I greatly appreciate all your help!3KViews0likes6CommentsRe: How to perform excel data query with multiple changing conditions
That worked perfect thank you! The only thing I noticed was if there was an autosave file that is the same number of characters length that was queried. Is there a way to only query "real" files? For example, ~$05 Jan 2024 Tasking List.xlsx which is 26 + 5 (31) characters which is the same as 05 Jan 2024 Retasking List.xlsx which is 26+5 (31) characters. I can definitely work around it and add a Text.Contains for "Retasking" vs "Tasking" but I wanted to see if you knew how to get around autosave or hidden files? Thanks!3KViews0likes8CommentsRe: How to perform excel data query with multiple changing conditions
Good Evening! Is there a way I can use Text.Length in the last step of the TodaysFile query to select the correct file? It is getting me to the correct folder aligned with today's date (Thank you so much for the help on this part!); however I have 3 files in that folder named as such: 05 Jan 2024 Tasking List.xlsx 05 Jan 2024 Retasking List.xlsx 05 Jan 2024 FM List.xlsx I was able named a cell in the PARAM sheet for the file I want and then call it in the using "#FileName" = ExcelWorkbook() >>> "Similar code to your folder path code" and then using the code to query a name that contains "#FileName" and it worked!...however, since "Retasking" and "Tasking" both have "Tasking" in it, it finds both files. I was wondering if I could name another cell with the number of characters the "05 Jan 2024 Tasking List" file would be as FileNameCharLength, then define it in the code using the same process I used for the filename, i.e., "#FileNameCharLength" = ...., then call it in query using Text.Length somehow since all 3 of those files will have different character lengths? Thanks!3KViews0likes10CommentsRe: How to perform excel data query with multiple changing conditions
Lorenzo #1 Yes. The format of the folder is as follows: 29 December ###, #####, & ##### with the "29 December" text changing daily to the appropriate date. Note. The yesterday folder "28 December ###, #####, & #####" will remain in the overarching folder as a archived backup; therefore, the only two folder to chose from are "28 December ###, #####, & #####" and "29 December ###, #####, & #####". #2 I am running Microsoft Office Professional Plus 2016 and I have no ability to update to an earlier version. #3 Yes. The target file is a Microsoft Excel Worksheet (.xlsx) with the format as follows: 29 Dec 2024 ######## #####. Thanks!3.2KViews0likes12CommentsHow to perform excel data query with multiple changing conditions
Hello All, I was looking to see if it is possible to perform a data query for a file that changes daily within a folder that changes daily... For example the folder is named 28 DECEMBER 2023 TEXT TEXT TEXT and the file is named 28 Dec 2023 TEXT TEXT TEXT and it get updated every day. I got it to work with the consistent name format and I am able to refresh it; however, that file will then be replaced with a different file tomorrow and the folder will be renamed. I am using an older version of excel but have access to Get External Data - From Other Sources - From XML Data Import and New Query - From File - From Workbook. I should be able to use Power BI as well.. Thanks!Solved3.8KViews0likes16CommentsData Validation List with multiple choices based on one cell (w/o VBA)
Good Afternoon, Please see the attached excel workbook for reference. I am trying to create a data validation list (Sheet: Flight Calculator - B13) to select all airports in a State (Sheet: CONUS Travel Source) based on a cell outputting the assigned state (Sheet: Flight Calculator - B7). ALL the airports for each State are in the CONUS Travel Source sheet and I need the data validation list to output all airports in accordance with the assigned State. I tried using IF statements but will run out character space within the data validation tool. I also tried using INDEX and MATCH function but could only get it output the first listed airport instead of list of all the airport. I also tried assigning each state a number/assigning each airport a number but no luck. Thanks in advance!Solved2KViews0likes8CommentsRe: Need help referencing duplicated values and matching for 4000+ cells
peiyezhu Please see the attached the mini sample workbook - my apologies for not doing this firsthand! All the white filled "NO GEO LOC FOUND" cells have an assigned value that is linked to a cell with a matching "ICAO - Filter" value which is highlighted red as it is a duplicated value. I need a formula that assigns the GEOLOC value to that matched value which will get rid of all the white filled "NO GEO LOC FOUND" values. Thanks! Marshall991Views0likes5Comments
Recent Blog Articles
No content to show