User Adoption
244 TopicsExtract 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 Sub53Views0likes5CommentsAutomating 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!156Views0likes10CommentsDouble 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 1960s1.1KViews2likes26CommentsSchool Master Calendar
Hello I am in charge of putting together a master calendar for our school community. This would be a behind-the-scenes calendar utilized for planning events and spaces within the school. I started out by using a calendar template in Excel called "Family Event Calendar." At first I thought this was going to do EXACTLY what I needed it to do---take a master list of events and apply them to a monthly calendar format in the workbook. Unfortunately, I ran into a problem where only one item will be listed for each day of on the calendar--as we are a school, there are usually a good handful of things on each date. With that, I started working on my own. I have successfully set up a workbook that has a master sheet that lists date, event, start time, end time, location, department, and category of the event. I have set up formatting rules to color code by the category column. I have figured out how to distribute from the master sheet to a separate sheet for each month. This alone will certainly be helpful for us in our planning. However, putting the items into format of a typical month calendar is desired. Here is a viewable link to the document I have put together thus far New Master Calendar Attempt 11 4 2024.xlsxSolved143Views0likes21CommentsComplex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like=IMSUM(A1:B1)or=IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the=IMABS(A1)and=IMARGUMENT()functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel:https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?19KViews1like3CommentsDax measures total mismatch in visual level
Hi Team, I have below dax measures as follows: Forecast - Target Units_1 = VAR _DailyUnits = [Target consumption per day_1] RETURN SUMX(Points, SUMX(Calendar_, IF(ISBLANK([Actual Units]),_DailyUnits ))) Referenced above measure 'Target consumption per day_1' comes from below: Target consumption per day_1 = VAR minDate = MIN ( 'Calendar_'[Date] ) VAR maxDate = MAX ( 'Calendar_'[Date] ) RETURN CALCULATE ( SUMX ( TargetTimeSeries, VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate] +1 VAR Result = TargetTimeSeries[Usage] / _Days RETURN Result ), REMOVEFILTERS ( 'Calendar_' ), KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ), KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate ), TargetTimeSeries[TargetType] = 1 ) The result(Duplicate of Page 3) tab is as below: Expected output total from Forecast - Target Units_1 should add up(4978+4037) to give 9015 in total. But the measure total displays as 18,030(guess so) which is not correct & what we wanted. Another example for above total mismatch is as below: I think its to do with filter context but am struggling to work it out. Please advise! PFA file herePortfolio Performance - v2.13 - Copy.pbix Thanks in advance! SergeiBaklanSolved501Views0likes9CommentsTo allocate stock from closing stock by formula
HI Guys, i've tried lots of times to get a solution for my problem. but still not sorted that. here attached my chart with two tables. in first table i have mentioned order number delivery date of that order number / required items order number wise and required qtys. in my second table i have mentioned items wise closing stock details. most of the items common for every order number. i need to allocate stock to first table from second table closing stock. for this i need to prioritize delivery date. as a example if i have 8000 pcs of closing stock in one item in second table and 1st table 1 have 5000 wise two deliveries. i need to allocate 1st 5000 for first delivery and balance 3000 for second delivery) any one can help for me?Solved12KViews0likes10CommentsCombining Two dax measures to avoid dax reference
Hi Team, I have created two dax codes as below: Related_(Target cost)_1 = VAR TargetValue = CALCULATE ( SUM ( Target[Value] ), 'Target'[DBName-Point_id] = MAX ( 'Accruals'[DBName-Point_Id] ) && 'Target'[TargetType] = 1 && 'Target'[month] = FORMAT ( MAX ( 'Accruals'[Date] ), "mmmm" ) ) VAR days = SUMX ( 'Accruals', TargetValue ) RETURN --days/[no of days] COALESCE ( days / [no of days], 0 ) Target cost_1 = SUMX ( 'Accruals', COALESCE ( [Related_(Target cost)_1], 0 ) ) Their results individually as shown below Is it possible to merge these dax measures into a single dax ? Also, final values at the total & column will be same as below ? I am doing this in order to avoid the reference of measures within another dax. Please help me. Thanks in advance! SergeiBaklan163Views0likes0CommentsDisable "save as" defaulting to OneDrive, but do not disable OneDrive - possible?
Today I had hundreds of users, including me being admin, being furious beyond imagination for following change which the last "slow ring" office brought along: You open a document from a network drive, my documents or desktop. You click "save as", and instead of defaulting to the path where the original document came from it defaults to onedrive. Every time. The amount of tickets from users missing their files they just saved is enormous. Since Winword 2.0 and Excel 3.0, both from 1991 where I used them the first time, the default of "save as" was always where the original file opened came from. How can we restore this original behaviour? We do NOT want to be Save-AS defaulting to a fixed place, like "my documents", we want the original behaviour, which we had over 30 years now, back. Can this be achieved? If you know please tell. We have to keep OneDrive, as optional save to place, just not as the default for any document. To us this is the single worst most expensive and time consuming change ever Microsoft has done to Office. On top it is a data protection law issue.Solved11KViews1like11Comments