What is the best way to filter a list of data with dual headers, using both those headers as the filter criteria? I’d like to extract 1 column of data to copy to another location using something like Advanced Filter to select 2 criteria ( Upper header and Lower header), and copy the one column with the matching header values.
The worksheet has the following peculiarities:
- 2-row headers
- Top row headers (main categories) are merged, spanning several columns (I can un- merge if necessary)
- Bottom row headers are sub-categories and have repeating names
Although the top headers are merged and the bottom headers have repeating names, each of the 2 headers combined creates unique labels for each column.
TAKING A BIT FURTHER THE POST Find Min Value In Multiple Columns And Match The Header
Here's my problem: I am trying to find the MIN value in a column with dual headings and match the corresponding product which makes my formula with three criteria.
see the attachment to see the structure of the tables and further explanation.
I'm using the code below to extract data from a 'Source' sheet to populate a "Yearly Extract Summary" 'destination sheet. With the unique distinct values copied from column I on the 'Source' ("All Data") sheet to column B on the 'Destination' ("Desired Output") sheet. In addition the values from column J on the 'Source' sheet are summed and paste under the relevant month on the 'Destination' sheet.
[Code] .......
The code works fine and the correct figures populate the correct columns and rows on the 'Destination' sheet.
As you can see from the code above, the monthly values have to be hard coded to match the column headings and this is fine when using a static 12 month period. But I'm now wanting to use a rolling 12 month period, which, at the moment, necessitates the need for me to change the code each month so I'd like to change the code but unsure where to even begin, how to produce the initial script.
I'd still like maintain the existing functionality in this section of code:
[Code] ...........
I have attached a file which contain 3 sheets.
The "All Data" 'Source' sheet,
The "Output" sheet, used for testing, and
The "Desired Output" sheet which shows the results using the current code
To run the code, please use the button at the top of the "All Data" sheet.
I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
I have a table of data with column headings. I'd like to be able to filter the table based on the column headings, however I have a 'spare row' between the column headings and the data which I use to add new entries to the table using a macro (the macro copies the row and pastes it in one of the empty rows at the bottom of the table, then sorts the data which places it with the rest of the data).
The problem is, Excel won't let me apply a filter to the table if the column headings are separated from the data that I would like filtered.
I have a table with unsorted Time Data and Headings for each Row and Column (See attachment). I need to display this data in another worksheet as a scrollable list.
Then when I click on any list item, it displays only that data in the following format:
A1 = Time. A2 = Row Heading. A3 = Column Heading.
Easier to understand if you view the simple spreadsheet attached.
I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
I've been trying to solve with a 2 column drop down. What I am hoping to do is use two columns to aid the user in selecting the correct entry. So the first column will be a number and the second column would contain what the number means
If: Col1 | Col2 001 | Horse 002 | Cow 003 | Dog
Both columns show when the list is selected, but when the user selects 003 | Dog, the cell would be populated with only the 003.
I have a simple table Attached. (A simplified version of a much larger table) The data continues on for thousands of entries and there are quite a few more columns. Names are dynamic in that names will be added and deleted over time. (There is a list of names on a different worksheet that updates as users are added.)
I wish to Consolidate the Data Under Different Titles. The data in the NAME column will Consolidate to list each name in Alphabetical order. The data under the RAP COUNTER column should count the number of YES entries per NAME under the new title ATTEMPTS. The data under the RESULT column should Count the number of KILL entries under the new title KILLS. And the Data in the TG SCORE column should average all entries per NAME under the new title TOTAL SCORE. I tried to do this with a pivot table to no avail. I could write some programming to accomplish this, but I am am amateur, and I also worry about time required to run a macro for thousands of lines.
I have to complete a task using Excel. Normally I would complete the task using a database, but this one needs to be complete using Excel (for others to use afterwards)
I have a flat semicolon delimited text file which holds x amount of lines OR I can get the data using an ODBC connection (directly from the DB) Each line starts with a date, a name, a second name, and a price. See: 1.input.jpg
The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.
My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.
The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).
I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range Columns AJ through AX Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I use a macro to format a debtors ledger. This report shows the amounts that customers owe broken down by the age of the debt, ie Current, 30, 60, 90, 120 days.
To make it easier to read I want macro code that will replace these mostly numeric headings with months ie April, March, February, January, December.
I want to save a worksheet as a CSV but Excel saves the column headings as the first line of the saved csv file. I don't want the column headings in the saved file.
The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns.
As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles.
Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?
I have a sheet where a name could appear multiple times in a 22 column data range. I would like to list all the names on a second sheet in Column A and in column B (and further columns as necessary) return the heading name of column where the name is found in cells next to the name.
I think Count If will work for giving me the number of times the name appears in sheet one but is there a way to return the column headings along the same row as the name?
I want to place this forumla "=IF(INT(B3/$U$1)=(B3/$U$1),1,0)" across my worksheet.
Now, I'm okay when dragging down as it works fine with the B3 changing to B4 and the U1 staying as U1.
However, I would like it so that when I drag it horizontally, the U1 becomes V1. I want it so that I can do this and it stays within the $. So when dragging sideways it becomes "=IF(INT(B3/$V$1)=(B3/$V$1),1,0)".
I have a spreadsheet which was created some time ago by an unknown person - I have the password for it and have unprotected it. On some of the worksheets the column & row labels are missing.
I am trying to write an advanced sorting macro that sorts on more than 3 columns. I need to poulate a combo box with the column headings, so that I can choose which column to sort on first, second etc. Now I am a complete beginner at this sort of thing, but I believe that ranges are required. I have tried using rowsource properties, but the combo box only ever displays one column heading.
I have a rather large workbook with a lot of VBA behind it (about 1MB with no data, just formatting and VBA code). It all seems to work perfectly, except one sheet. It is a list of about 35 options with checkboxes beside each option. When the sheet is made visible through VBA code, the user has problems when scrolling the sheet.
It's hard to describe, but it all looks perfect until either the scroll-wheel or the scroll-bar is used to move around the sheet. The display then goes completely screwy. The heading row (which is actually a frozen pane anyway) ends up repeated all down the sheet, as do the column headers (A|B|C|D|E...). If i select another sheet and then back to this one, it's fine, it appears to be only when it is un-hidden through VBA.
I have attached two screenshots as an example... the only thing I have done between the un-scrolled one and the scrolled one is scroll the mouse wheel down and back up once.
Is there a way I can return a column heading(s) if text is present in a row?
Refer the attachment... ozgrid.xls
Names are listed down the left, headings along the top, with data in the corresponding cells. For each unique name i.e 'Steven' is there a way to list each heading data is found i.e 'Shop2, Shop4'???
I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????
Simple macro formula. I want to have my function to insert a column and copy over some data headings here is what I have...
Sub Macro17() ' ' Macro17 Macro ' ' Selection.End(xlToLeft).Select range("D5").Select
[Code] .........
The issue I am having is that is it inserts the column AA (end of the original data) and I want it to insert at the end of all the data. For example if I run the macro once it posts in column AA, I want the next result to post in AB but it posts in AA.
1) I have 2 worksheets that contain 2 matching name columns; " Date" and the primary column A "Symbol". The rest of the column headings in each of the worksheets are different. Data (from internet sources) is inserted into the worksheets in table format on daily basis
Sheet 1 name = Data 1 Sheet 2 name = Data 2 Master worksheet name = MERGED DATA
2) I use the Match and Index formula for both sheets
the following formula is inserted in row 2 of Data 1 and Data 2 sheets (other than Col A "Symbol")
