Working with Arrays of Ranges
OK, so strictly speaking an array of ranges is an error in Excel right from the outset. However, I set up an array of tables contained within the named Range 'allocatedRange', here shown with a red outline. I then set up a function that would return the range corresponding to a country SelectDataλ = LAMBDA(country, LET( countries, FILTER(TAKE(allocatedRange, , 1), markers = "Country:"), recordNum, FILTER(SEQUENCE(ROWS(allocatedRange)), markers = "Country:"), recordEnd, VSTACK(DROP(recordNum - 1, 1), ROWS(allocatedRange)), rangeRows, recordEnd - recordNum, countryArrϑ, MAP( recordNum, rangeRows, LAMBDA(start, rows, LAMBDA(TRIMRANGE(TAKE(DROP(allocatedRange, start), rows)))) ), XLOOKUP(country, countries, countryArrϑ)() ) ) The start and row count of each table is determined by using the string "Country:" as a marker and differencing row numbers. A combination of TRIMRANGE/TAKE/DROP picks out each range in turn and assembles them into an array by turning the range references into thunks (TYPE=128). The function SelectDataλ is used to look up any specific country and return the corresponding range. To demonstrate that the function indeed returns ranges, the COUNTIFS function is used to count the number of cities within each country that have a population exceeding 100 000. = LET( countries, FILTER(TAKE(allocatedRange,,1), markers="Country:"), countLarge, MAP(countries, LAMBDA(country, COUNTIFS(TAKE(SelectDataλ(country),,-1), ">1000000") ) ), HSTACK(countries, countLarge) ) The point of this post is to introduce the idea of treating tables as elements of an array, so allowing further tables to be inserted or removed dynamically. TRIMRANGE ensures that each range is sized correctly and MAP is used to analyse each table in turn. Whilst Excel throws hissy fits at being asked to store arrays of arrays, arrays of ranges, or nested arrays, it is perfectly happy to store arrays of functions. When evaluated, each element of the function array is perfectly free to return an array or even a range. The effect is to permit Excel to process 'arrays of Tables' faultlessly.
Extract Same Table from Multiple PDFs
I tried to modify the recorded macro to accept another PDF in table 002 with the next monthly summary table (table001 & table002 confusion). All of the files are in the same folder numbered incrementally, 1-12 (Jan-Dec). There are other similar folders for different accounts requiring the same procedure be performed and are set up in similar increments by account. Would like all of the monthly summaries from the PDF table 001 listed within one worksheet or workbook. How can this macro perform the function described? Sub ExtrctMonthlySum() ' ' ExtrctMonthlySum Macro ' ' ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""T:\Traverse\Church International & Triumphant\DCTC\Financial\Treasurer\2024\Acct 1893\2.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [Table002 (Page 1)]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table002__Page_1" .Refresh BackgroundQuery:=False End With Range("C5").Select End Sub
Graphing Multiple Series on one Graph
SampleSheet Hello all, I have been asked to Graph a variety of different data series into one graph to see if any may relate to each other. The graph is intended to be a line graph, having 5 different lines, representing 5 different data series. I believe this is possible, but just cannot remember how to do it. An issue that I'm having is that the Data series has such different values, ranging from a series having values of <1.0 to series having values of >20,000. Is it possible to have a 'break' in the y-axis so that the gap between say... 100 on the y-axis to 20,000 on the y-axis is non-existent or would it be recommended to just add a secondary Y-axis for this series? The dates are of significance, and should be on the x axis to show the timeline of these different values. Any help here would be appreciated as my direct report has been asking this of me, and we're both stumped. I've attached a spreadsheet with the 5 series included. Is anyone able to have these 5 series added to the same line graph in a legible manner without adjusting their values? Thank-you so much in advance. Toon
Power Automate/Query Assistance - Add new table in file to bottom of existing/trend assistance
Forgive me if this has been answered elsewhere, I have had a look and not been able to find! Essentially, I have set up a flow using Power Automate that saves an XL I receive via a scheduled email into a sharepoint location. I have then created an excel document that queries this file in order for me to maintain a daily report. My issue is this. I am currently trending the data, but the only way I have of doing this is by copy and paste into a separate sheet (adding to the bottom of the previous data each day/week (select timescale) manually) as the data gets overwritten on each data refresh. I'm sure there is, but is there a way via either macro or power automate of me automating this process so that each day the table in the new file is just added to the bottom of the existing?? Thanks in advanceAutomating Party Transactions in Excel Using VLOOKUP and VBA Macros or anything
Hi everyone, I have a main sheet named "DAILY INWARD OUTWARD" that contains transactions for over 50 parties. Each party does multiple transactions, and I need to manage their stocks. Currently, I manually create separate sheets for each party and record their transactions, which is time-consuming. See this is my master sheet and I make stocks like this That month Jodhka made 4 transactions, so by searching in master sheet all over made manually my diff party list. likewise i have to maintain 100+ party details, so anyone can help me reagarding this? I would like to automate this process using VLOOKUP and VBA macros or anything. Specifically, I need help with: Using VLOOKUP to fetch transactions from the main sheet to individual party sheets. Writing a VBA macro to create separate sheets for each party and copy their transactions from the main sheet. Any guidance or examples on how to achieve this would be greatly appreciated. Thank you!
How to delete multiple cells based off the value of a specific cell.
I have an excel file with multiple sheets. I was wondering if there was a way to complete this task. When I delete the value in the column of "Hose"(A), I would like it to automatically delete everything in C9:K9. Looking at the image uploaded, when I delete "811HT-24", it would then delete RCC,x,7458,1x24,2x24,3x32,1x48 if it worked like I'm hoping. In the B column I'm using VLOOKUP tied to an index. All of the sheets are identical as well. Any help would be appreciated.Double Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n(not just a count of options =COMBIN(n,m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960s
