Recent content by matt-gilbert

Thanks so much p45cal. I thought it might be easy but it looks quite complicated (to me). I'll try and learn how you've done it! Thanks again

Hi all, I have a bad dataset that contains headers and records (looks like a pivot table in compact form). I would like to unpivot and create new columns for the different headers (in example attached this is Country, State & City). I have added a column called "Type" where I have assigned type...

Hi Mohamed, wow! I suspected it was possible to do using a formula rather than VBA or power query, but did not realize it would be that complicated. Thank you so much!

I receive data from our clients that has multiple header rows. I need to concatenate these headers into one single row so that I can pivot this data. I know this can be done using a power query however would like the ability to use a formula. Have tried various formulas using COUNT, LOOKUP etc...

Thanks @p45cal . Refer my reply above to @vletm . I have used both of your suggestions. Appreciate your help! Matt

Thanks @vletm I didn't even consider the MINIFS, AVERAGEIFS and MAXIFS. I did make a slight tweak to your formulas as follows; in I6: =MINIFS($O6:$AD6,$O$3:$AD$3,$B$6,$O6:$AD6,">0") in K6: =AVERAGEIFS($O6:$AD6,$O$3:$AD$3,$B$6,$O6:$AD6,">0") and in M6...

Hi all, I have a quote comparison sheet which has a section in which to analyze supplier quotes minimum, average and maximum costs. The problem I have is that I need to manually adjust formula when using for another commodity type. I would like this formula to automatically adjust for different...

Hi all, I have been provided with some data that appears to have been produced from a pivot table. The data (1 column, 22 rows) has also been provided with the original level (or indentation) number. What I am trying to achieve is to write one formula that can convert the data back into the...

Hi p45cal, I've finally got it all working (see attached). Just wanted to let you see the outcome to close it out with you and help out others who might need this for calculating tax rates. Thank you very much for your help with this. Matt PS - I have no idea why the ATO truncate and add .99...

Hi p45cal, sorry maybe helper wasn't the correct term. I have updated my spreadsheet to show a larger dataset and the formulas that are currently working that I'd like to replace. I have tried modifying your formulas (cells P1 & P2) however when I paste them into the cells below they return the...

Hi p45cal, Wow! Thanks you for your comprehensive response. Just when I thought I knew a bit about Excel... I was hoping to copy formula from J10 and repeat down page for a larger data set (I'm going back over the years to calculate how much tax I should have paid for various different pay...

Microsoft 365 Version 2207 Build 16.0.15427.20182 64-bit

Hi, I have 2 values (date and number) that I need to do an approximate (less than) lookup on 2 columns. I have attached relevant file with my current VLOOKUP formulas highlighted which only have 1 lookup value (number). I'm trying to add the date to this formula so that the lookup range will...

Hi p45cal, works perfectly. I didn't quite articulate exactly what I wanted in my initial post but your first solution is exactly what I was after ie having the source worksheet sheet name in every row in column A. Many thanks! Matt

Hi all, I have the following code which combines multiple worksheets in a workbook into one sheet called "COMBINED". I would like to modify this code so that it includes the source sheet name in column A of the "COMBINED" sheet and have all of the worksheets pasted from column B onwards. Any...