Ceating a macro to perform a look up and return based on column headers.
General Synopsis: Using a call and parameters to choose a lookup column and return column, and their respective sheets. Then the sub routine finding their respective column headers and perform a lookup/return. Essentially the current problem is the macro runs to slowly.
Link to original post: Lookup Macro based on column headers
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.
View 5 Replies View RelatedI have a small sample table that replicates what I am trying to do
A
B
C
D
E
1
Bob
Jim
Mary
Sue
[Code] ........
I would like to have excel put in compile the list of members on Teams 1 through 4. So in A7 through A10, I will put the teams and then in the B column I want the compiled list seperated by commas for each team so that they read like this...
Team 1: Bob, Jim, Mary, Sue
Team 2: Bob, Jim, Sue
Team 3: Bob, Mary
Team 4: Bob, Sue.
Of course this should be dynamic so that if I place an x in C4, then Team 3 would read as Team 3: Bob, Jim, Mary.
I have tried various index match offset combinations and did not have any success.
I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.
I've attached an example spreadsheet for reference. Basically, I need two formulas:
1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).
My actual data file has 80+ senders and 100+ receivers.
I have a column with rows having strings as below (see sample.xlsx excel attached) .
I want to delimit the string in such a way that the for each parameter, the parameters will beome columns with headers and the value against them will be shown in thier respective column. The output tab in the attached excel might make what I am trying to convey more clear.
So basically CPU should have its own column and its time against it.
There are thousands of such column that we wish to analyse and therefore the ask.
I tried various option to delimit including recording a macro but did not work
CPU: 5,622.871 ms, Sync: 0 ms, Wait: 0 ms, Suspension: 2,399.921 ms
I'm having an issue where I have a macro (see attached file) that is supposed to auto-fill and sort. Twhen it tries to run the auto-fill on a sheet in its blank/template state. It will take my formulas in columns AC:AZ and fill them up to the header row.
[Code]....
This code was hobbled together using record macro and my minor understanding of coding.
(CK) Macro issue.xlsm
on a vb user form list, made from the control toolbox
I enable collumn headers but have trouble populating them
From what i could get from google, it seems the only way to populate them is by having the data on an excel sheet. Can you just do it through code?
I have another list which the data is on an excel sheet but I can't get my headers working.
I have been using
frmAct.listCodes.RowSource = ("A1:C39")
frmAct.listCodes.RowSourceType = ("Value")
It doesnt like "Value"
In the attached file is it possible to use cell/ array formula in cells P3 to R6 to lookup names (Column O) within the data range (Columns A - M) and return the values shown in the yellow shaded area?
View 8 Replies View RelatedI have been given a spreadsheet with standard configuration with column headers etc, but when you scroll down the sheet the actual column headers change from the normal A-Z display to be the column headers that have been entered into the cells.
I know this can be achieved similarly by setting Freeze Pane but how this other option has been switched on. I have attached the spreadsheet (which has totally fictitious content).
I have data as follows in Column A:
Part Number: 0000000-1 ARTEC-GH-56S 12A
SPARES in Repair: 20
On-hand: 100Location: BNCD
I need the data under different columns as follows: I also want an extra column before Column A labelled as Common number.
A B C D
Part Number SPARES ON HAND LOC
0000000 20 100 BNCD
I have a a client who sends me an excel workbook quarterly. There are 90 worksheets and each has 20 language columns which are suppose to be the same on each worksheet.
My Problem is I want to merge them into one worksheet so i can add them to a database, but the client keeps changing the order of the languages!
So as an example I need to be able to identify the Polish, Russian and English columns on every worksheet and then combine each language to a new column on a new worksheet.
I'm trying to calculate a figure in a report tab that reads off the data tab and looks for a specific column based on the header of that column. My explanation is admittedly poor but I've attached a sample file with an outline of what I'm trying to do which I hope makes things clearer.
Sample File.xlsx
I have a spreadsheet an excerpt of which is as follows:
K L M N
1 Doors Security Vehicle Key
2 TRUE FALSE FALSE FALSE
3 FALSE TRUE TRUE FALSE
4 FALSE FALSE FALSE FALSE
Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".
In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.
I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?
I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row.
I have weekly dates in columns along the top of my spreadsheet on row 3:
C3 = 07/11/08
D3 = 14/11/08
E3 = 21/11/08
etc...
The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B.
I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row.
For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18.
I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.
I have a 3 column table with 20 rows. The first two columns contain travel
expense data. The cells in the third column are blank exept for whichever
cell I enter an "x" in. Please let me what formula or worksheet function do
I use to calculate the travel expenses based on the data in the first two
columns that correspond to the row of the cell with an "x" in the third
column?
I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.
My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.
However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.
I have a nice little code which imports data from a mastersheet to my input sheet. Though it will only work as long as the mastersheet is static.
I'm trying to match the product code in column A between my mastersheet and my workbook with a input sheet. And then copy the cell value in column D and E of the relevant row.
The messy part is that some of the products are split into sub categories (less than :$$$:, between :$$$: etc) and they dont have any info in column A.
I'm unsure if this is doable. Is it? And if I get permission to edit the mastersheet somewhat.
I tossed in the start of my currect static c/p, thought I don't think I'm keeping it if I get a handle on this match macro issue.
Code:
Dim ws As Worksheet, wb As Workbook, t As Date, wb1 As Workbook, wb2 As Workbook, wbName As String, janei As String, spm As String
Dim fil
Set wb1 = ActiveWorkbook
ChDir ""
[Code] .........
I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
My and a work college needed to combine our separate excel worksheets into a single document.
Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.
After we finished importing we exported the final workbook as .xls (so I could open it).
After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)
Also I am unable to use features such as "Freeze Pane"
I suspect this was caused by importing and exporting through open office?
How do I set up the formula bar so it doesn't cover the column headers?
View 1 Replies View RelatedI want to return the column headings (the months) where there is a 'No' value, per person. How to do this?
Here's the screenshot
Screen Shot 2014-03-17 at 11.45.34 am.png
And here's a dummy version:
Example Spreadsheet.xlsx
I have a table with headings for each column.
How can I make the columns headers buttons/clickable, so that it sorts the rows on the column that was clicked upon?
I want to include columns in my Pivot Table where there is no data for that column. For example, I want to show 12 columns, one for each month, but my data only has 9 months of values.
View 3 Replies View RelatedMy spreadsheet has in the region of 30 columns, more will be added on occasion in the future and ultimately I want to have each of the column headers appear in a 2-tiered dependent combo box. In the following structure:
Category 1:
Header 1
Header 2
Header 3
Category 2:
Header 4
Header 5
Header 6
Header 7 etc...
What I'm not sure about is what the most efficent way of making it so that it will automatically add new Column headers (and possibly categories) to the drop-down box so that it does not need to be re-coded in the future.
I want to name three sheets (sheets will have a different name every month so I refer to them below as Sheet(1), Sheet(2), and Sheet(3) (Their sheet index)) using a vlookup in VBA. I want the code to promt the user for input and based on the entry, I want to use a vlookup to name the sheets. There are three "lookup tables" on a sheet named "Ref" that I have defined as Table1 (used for naming Sheet(1)), Table2 (used for naming Sheet(2)), and Table3 (used for naming Sheet(3)) that I want to reference in the code. I also want a code to check the first MyLookUp1 which I have attemped below, but I do not know if I am doing this correctly. Below is my bad VBA attempt so you get an idea of what I am trying to do. Also, attached is an exaple file.
Sub namesheets()
Dim MyEntry As Variant
Dim MyLookUp1 As Variant
Dim MyLookup2 As Variant
Dim MyLookup3 As Variant
Dim MySTRING As String
MyEntry = Application.InputBox( _
Prompt:="Please enter an Item:", _
Title:="Lookup sheet name", _
Type:=2)
MySTRING = MyEntry
MyLookUp1 = Application.VLookup(MySTRING, Table1, 2, False)
If IsError(MyLookUp1) Then Exit Sub
Sheets(1).Select
ActiveSheet.Name = MyLookUp1....................
The logic of the formula like this....
TAB X, Column A contains a data set, each row is a unique value. In column B of TAB X, I want to input a formula that performs the following logic: Search TAB Z, Column C, for an exact match of data contained in TAB X, Column A....and if found, look in TAB Z, Column D...if a blank cell is contained there, then in TAB X, Column A, say "Unsettled" ; however, if there does exist any character (not-blank) in TAB Z Column D, then in TAB X, COlumn A, say "Settled".....but, if the data ID listed in TAB X, column A is not found whatsoever in TAB Z, Column C, then say "NOT FOUND". So for example, within TAB X, Column A there is a unique ID in one of the rows "ABC123", then searches "ABC123" in TAB Z, Column C. If found, it looks in TAB Z, Column D and that cell is blank, so TAB X, Column B, same row that contains ABC123, the result is "Unsettled".
I currently have a worksheet filled with data from other worksheets. I want to combine data from different columns when they share an identical row header and don't know how. For example:
1b--
1--c
1-d-
2-x-
2y--
2--z
Would go to:
1bdc
2yxz
Another specific issue is that some row headers are bold and need to be considered a separate header from the non-bold alternative.
Is there any way in Excel to create single-cell named ranges from a combination of the labels in the rows and column of a spreadsheet.
Here is an example:
CompanyA
CompanyB
CompanyC
CompanyD
Sales
100
200
[code]....
I would like the first cell (containing the 100) to have a defined name of (something like) "SalesCompanyA", and the second cell to be "SalesCompanyB". And so on - e.g. the cell with 300 in it should be "CostCompanyA".
I can do it manually, but I have a huge spreadsheet & was hoping it could be automated. Just to be clear, it needs to be a single cell range. I know you can create a range from a selection - but this seems to create ranges of the entire row and/or column.