I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.
I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.
My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))
I have an XY plot where the source X value data is a series of time values, each associated with a descriptive text cell. I would like to select a single point (obviously by mouse) and then execute a macro (right click preferred but a separate command button would be a suitable substitute for now) that will extract the X-value. I then plan to use that value to find within the source data the associated descriptive text cell and make a data label from that text. No clue where to start...the macro recorder is woefully deficient in this area.
I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.
In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example, Column AK has drop down list with following options:
Good Moderate Poor
When a user selects one of the options, I want one the following respective letter to appear instead......
I want to import one worksheet from different workbooks to Master workbook. In the source workbook, I need to import only ' Code' sheet and in this code sheet , I need only 4 columns(A, E, F, and I ) to import. I also need cell C3and E3 in source worksheet to master workbook
I want to import like 'Code(1), Code(2), Code(3),, and so on. I don't want to import whole thing in the one sheet in the master workbook.
IN the master workbook, the header is on A3, so starting row will be row 4 in master workbook.
I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.
There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.
The next pressing of the macro should then unhide all the hidden columns.
I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.
Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)
If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.
I have a long list of 2 columns containing data as follows: country1 date1-1 country1 date1-2 country1 date1-3 country2 date2-1 country2 date2-2 country3 date3-1 country4 date4-1 country4 date4-2 country4 date4-3
which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g. country1 country2 country3 country4 date1-1 date2-1 date3-1 date4-1 etc. date2-1 date2-2 date3-1
I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached) Raw data in columns A,B,C,D,E as below
When I select multiple cells horizontally at the top are highlighted columns for those cells.
I would need to know the number of those columns (es. 5), "on the fly", without having to rely on eye every time. This number maybe displayed in a status bar or other window...
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select". 2) Is there a way to select the workbook from were to copy the column without needing it to actually open? 3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.
I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...
So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..
I made my own small code but dont know how to make it act differently on selected columns....
I would select the entire matrix and then want the macro to run differently, taking into consideration the column names
[Code].....
I also need to add a vlookup formula for some column based on the column B's value...
Ex: This is for Col C
[Code] .....
So how do I write the code in such a way that the code acts on its own differently.
How can I adapt the following code to only show Columns; A, B, E, F and J from Sheet(3) into a Listbox called lbx_LiveAllocations?
As it stands, this code is only adding Column A. My only alternative has been to add all columns on the worksheet to the listbox, however there's a lot of unneeded information between.
Code:
Dim LR As Long Dim ctrl As Object Dim i As Long Dim dic As Object Dim arr As Variant
My starting point is a big data set that needed to be validated by several users. I split the data set into smaller sets that were sent out. All users have filled in the same column called "Input"
What I would like to be able to do is to paste their input into a single column on my original data set and keep the name of the input next to it. Is it possible to paste without overlapping prexisting data ? Something of the likes of F5 ("Select blank cells/text") ?
I've attached a simple file with dummy data to explain myself.
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.
I am having data in two columns (A & B ). Now I want to get the data in the single column(column C) . Here column A(Name) & Column B(Mobile No). it is something like below.
Here is my input data:
I want to get output results like below in the column C
In the above (repeating for say, 50 or more cartons), is there a way with a macro(I know nothing about) or pivot table(see macro) to get the following format/result?
I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.
I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.
Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.
How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional. I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.
I have two excel sheets exported from two sources of information. Both sheets contain 1 column which is the primary data, ie, there is no duplicate of that information. Each information in these column represent 1 device in the real world.
Now, for example: In one sheet, I have: SDV0620B In the other sheet, I have: SSDV-B0620B
Please note that, both data actually represent the same device. What I now need to make sure is that, I ccan identify each corresponding data from the two sheets.
So, here is the problem that I am facing, identifying two data which are same but has two different representation techniques. (since the data are from two different sources)
I came up with the idea that, if I can check the sequence of characters with one column to another, then I might be able to pull it off.
For example, we take SDV0620B, and match the sequence of character with SSDV-B0620B. therefore, first is S (from SDV0620B) match with SSDV-B0620B then, D match SSDV-B0620B then V match SSDV-B0620B and so on, until the end.
And if the 1st sequence is found in the second sequnce of characters, then it is called a match! Hope I have explained it clearly. I have provided a file with some of the data copy and pasted out, to give you an idea of what the values look like.
