Search results

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...

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...

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, 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 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...

Hi all, I have a spreadsheet (attached) where I record lotto number selections and results. I would like to modify a couple of formulas as follows; Column K - I currently have to manually adjust each week to change lookup range of results eg in cell K3 it refers to C$17:I$17. Formula would need...

Hi. I am trying to automate a spreadsheet to calculate manning levels per roster crew as per attached spreadsheet (range C24:V27). I have tried many types of formulas (COUNTIF, OFFSET etc) without much success. I'm thinking it might be too much for a formula given the variables involved. Any...

Hi. I need to create a dependant data validation list based on the adjacent cell value. I have done this before as per link below http://www.contextures.com/exceldatavaldependindextablesindirect.html This method however does not suit my requirement. My spreadsheet is attached and my requirement...

Hello. I'm trying to apply VBA to conditionally format a table as per attached file. Specifically, I would like to use the table header references within the code. I can do this manually via the ribbon however Excel has a tendency to fail with conditional formatting when you insert rows hence...

Hello. I have a workbook that I use to create a report for our client. It has 5 sheets and each sheet has it's own Data Connection (DC), 2 from Excel files, 3 from our company's proprietary software ie a .rpt connection. I have written a macro to filter each of these DCs, copy and paste visible...

Hello, The intent of my spreadsheet is to use it as a 1 stop shop for pricing and tracking project variations. It is structured as follows; 1. Register - Summarises all data from Variations sheet 2. Variations - Data entry sheet to populate all variations information 3. Database - List...

I need a formula to provide the max date of each item. Formula needs to be in Column C of attached spreadsheet. Thanks in advance. Matt

I have an Excel table for entering timesheets into which needs to be converted into a database format for uploading into our accounting software. I have attached a file to show you what I'm after. Thanks in advance. Matt

Hi all, I am trying to track revisions of drawings. Drawings have text revisions when they are not approved for construction and then revert to numbers when approved for construction. For example, in column A, Drawing No's are as follows with Revision No shown after the underscore; Drwg No...

Hi all, My data in column A has duplicates. I am trying to write a formula in column B to identify the occurence number count of each duplicate. Desired results as follows; A B Red 1 Yellow 1 Green 1 Yellow 2 Blue 1 Red 2 Yellow...

Hi, I am creating a spreadsheet as follows; Sheet 1 - User entry form. Range A2:E10 have dependant drop down lists and a fixed set of headings in Cells G1-Z1. Lookup results will be displayed in Cells G2:Z10. Sheet 2 - Lookup database. VLOOKUP data in Range A2:E1000. HLOOKUP data in Range...

Hello, I have been given code numbers similar to above and need to extract the alpha and numeric characters separately. Examples of code numbers as follows; A1.1 A10.1 A10.10 AB1.1 AB10.1 AB10.10 As you can see each field is not fixed so the formula needs to adapt to suit. I have used the...

I'm trying to create a basic Gantt Chart to plan our future workload for our estimating dept. I would like to be able to automatically merge the repetitive cells that contain the same year (ie on row 2) and also automatically merge the repetitive cells that contain the same month (ie on row 3)...

I have a pivot table which shows the weather statistics for the past 50 years (rainfall & temperature). My pivot table has a Report Filter based on Year. I start with a Worksheet called Weather Data where all the raw data is input. From this I have created a Pivot Table and placed this on a...