User Profile
Rodrigo_
Steel Contributor
Joined Nov 10, 2022
User Widgets
Recent Discussions
- 37Views0likes0Comments
Re: Help with formula in Exc
As long as you put it right it should work by now.. Put the sheet name on cell N1, then the formula must have a bracket {=formula} at the beginning and at the end, like this: {=IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"")} then drag the formula across the table Master Sheet: Lead Sheet: Install Sheet:19Views0likes0CommentsRe: Help with formula in Exc
Yeah, did not consider if you're using an older version. Do this instead: Type manually the sheet name on the cell of that sheet wherever you like, in my case it's on cell N1 of Lead sheet, Use this array formula on cell A2 (double click the cell to enter in Edit mode) of Lead sheet: =IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"") Upon putting the formula on the cell press CTRL + SHIFT + ENTER, instead of pressing enter. Then drag that formula to column H and then drag it again to bottom of your table.87Views0likes2CommentsRe: Help with formula in Exc
The "Type" column from master sheet must be match with the other sheet's name, Then, use mid and cell function to extract your sheet names, and use filter to return dynamically the data from master sheet, and will update whenever you input more data on the master sheet. Put this formula to cell A2 of the other sheets. =FILTER(Master!A2:H15,Master!B2:B15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"No data")68Views1like4CommentsRe: Excel graphing help to show grouped data
You first need to make sure sort your table/data so that the regions (e.g., Kimberley, Cranbrook) are grouped together, and within each region, the 2020 data comes above the 2015 data. To do that: Select the entire table. Go to Data → Sort. Sort by Region Name (A to Z) as the first level. Add another level to sort by Ref_Year (2020 first, then 2015). Then create a stacked bar chart.172Views1like1CommentRe: Pivot many to many relationships
Another variant/option for you, this is a different approach if we're not doing pivoting. Just simply put the given formula in one cell, and it will spill out the results. Make sure that the covered cell of that formula is empty. Since that formula essentially creates a summary table that combines dates, gains, and expenses from two different tables, calculates the totals, and presents the data in a structured format, using LET function. In the formula itself it contains header for the table, data (date, values), and totals, net gain here's the revision of the formula for easy editing of ranges. =LET( header,{"Date","Gain","Expense"}, dateCol,SORT( UNIQUE( VSTACK( FILTER(Table1[date], Table1[est]=L1, ""), FILTER(Table2[date], Table2[est]=L1, "") ) ) ), gainCol,IFERROR(XLOOKUP(dateCol,Table1[date],Table1[gain],0),0), totalGain,SUM(gainCol), expenseCol,IFERROR(XLOOKUP(dateCol,Table2[date],Table2[expense],0),0), totalExpense,SUM(expenseCol), gainexpense, (totalGain-totalExpense), totalrow, HSTACK("",totalGain,totalExpense,gainexpense), combinedData,HSTACK(dateCol,gainCol,expenseCol), IFERROR(VSTACK(header,combinedData,totalrow),"") ) What needs to change to your actual table/ranges: EST Ranges: Replace Table1[EST] and Table2[EST] with the actual establishment column names in both tables Column Names: Replace Table1[date] and Table2[date] with the actual date column names in both tables. Replace Table1[gain] with the actual gain column name in the gain table. Replace Table2[expense] with the actual expense column name in the expense table. Purpose of Each Part inside LET function: Header: Defines the headers for the final table: {"Date","Gain","Expense"}. Date Column (dateCol): Combines and sorts unique dates from both tables where the establishment (est) matches L1. Uses FILTER to get dates from both tables. Uses VSTACK to stack the filtered dates. Uses UNIQUE to remove duplicates. Uses SORT to arrange the dates in ascending order. Gain Column (gainCol): Uses XLOOKUP to find the gain values from the gain table for each date in dateCol. IFERROR ensures that if a date is not found, it returns 0. Total Gain (totalGain): Calculates the sum of all values in gainCol. Expense Column (expenseCol): Uses XLOOKUP to find the expense values from the expense table for each date in dateCol. IFERROR ensures that if a date is not found, it returns 0. Total Expense (totalExpense): Calculates the sum of all values in expenseCol. Net Gain (gainexpense): Calculates the difference between totalGain and totalExpense. Total Row (totalrow): Creates a row with the total gain, total expense, and net gain Combined Data (combinedData): Horizontally stacks dateCol, gainCol, and expenseCol. Final Output: Uses IFERROR to ensure that if there's an error in the final table creation, it returns an empty string. Uses VSTACK to stack the header, combined data, and the total row to create the final table.4Views0likes0CommentsRe: Pivot many to many relationships
Late reply, see the sample file here> https://filetransfer.io/data-package/7YLnIWr5#link Content: (EST1) (EST2) Formula: =LET( header,{"Date","Gain","Expense"}, dateCol,SORT( UNIQUE( VSTACK( FILTER(Table1[date], Table1[est]=L1, ""), FILTER(Table2[date], Table2[est]=L1, "") ) ) ), gainCol,IFERROR(VLOOKUP(dateCol,Table1,3,FALSE),0), totalGain,SUM(gainCol), expenseCol,IFERROR(VLOOKUP(dateCol,Table2,3,FALSE),0), totalExpense,SUM(expenseCol), gainexpense, (totalGain-totalExpense), totalrow, HSTACK("",totalGain,totalExpense,gainexpense), combinedData,HSTACK(dateCol,gainCol,expenseCol), IFERROR(VSTACK(header,combinedData,totalrow),"") )71Views0likes2CommentsRe: Help with SUMIFS formula
Or use this sumproduct like this: =SUMPRODUCT((A2:A100=$H$2)*(C2:C100="Incremental")*(F2:F100="Y")*(D2:D100<=B2:B100+90)*( (D2:D100<B2:B100)+(D2:D100>B2:B100) )*(E2:E100)) to be more flexible, and ensure the date in column D is within 90days before or after in column B by row.24Views0likes0CommentsRe: Text Paring with a "\n" New line char
Here's my approach: Replace New Lines with Another Character: Press Ctrl + H to open Find and Replace. In "Find what," press Ctrl + J (represents a newline). In "Replace with," enter a delimiter like a slash ( / ). Click Replace All. Use Text to Columns with your chosen delimiter to split the text.138Views1like1Comment- 95Views0likes3Comments
Recent Blog Articles
No content to show