Merging Cells Contents Based On Unique Identifiers
Feb 22, 2010
I’m having some problems writing a macro,
I have a spreadsheet with 2 columns. The first column is the index number (unique identifier) of the second column’s data that will repeat itself as many rows as needed. Let me try to exemplify it:
Column A (index)Column B (text)
1 AAA
1 BBB
1 CCC
2 DDD
2 EEE
3 FFF
3 GGG
3 HHH
3 III
I’m trying to write a macro that will merge the text of each identifier in the first row it appears. The result I’m looking for is:
Column A (index)Column B (text)
1AAA
BBB
CCC
2DDD
EEE
3FFF
GGG
HHH
III
I already know how to make the merge, but I can’t get the logic to make it go through each row and merge the contents, since the number or rows for each identifier varies.
I am trying to give a cell a unique ID based on the contents of 2 other cells.
Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
Our unique identifiers are from SF.com... they have created unique Account ID's.
Comp Name Unique ID Company A - 5102AA Company B - 5102Aa
The problem: Excel does not recognize the capital A and the lowercase a as being different. I need to pull this data out and bounce data across tables for analytics.
The Question: Is there any way I can get Excel to recognize 5102AA and 5102Aa as being DIFFERENT ID's?
I would like to add a column from another spreadsheet which also has unique identifiers - but some may be missing and there may be some missing so cannot simply cut and paste over
I have a sheet with employee attendance (in hours attended per session) at professional development. Each row in the sheet represents one employee-session. The key columns are as follows from L-R:
Employee ID, Provider, Date
Note that if an employee has attended multiple sessions, each is represented in its own row.
What I would like to do is have each row represent an employee, and have an individual column for each session. This way, if an employee has attended multiple sessions, I have their hours of attendance all in the same row.
So far, I have figured out how to transpose each unique session into its own column header, and I have entered a VLOOKUP function to get the hours of attendance for each session in its respective new column. But these are still spread out over separate rows for each employee. Again, I want to essentially collapse these rows into one row per employee.
I am a hobby programmer trying to automate data entry into SAP. With GUI scripting switched on, i believe it is possible to get the unique identifiers of any field or control on a SAP screen. I am wondering if there is a dll or some mechanism whereby i would be able to get a tree structure of control identifiers for a particular screen so that i would not need to go through each control individually.
Sheet 1 has a list of unique ID numbers for several people. Sheet 2 is a master list of unique numbers for the entire population plus emails. Can i use a formula in sheet 1 to match the emails from sheet 2 to the unique id numbers in sheet 1?
I have a sheet over data with over 8500 entries in. One column us a list of item numbers, the second the packaging requirement code, and the other is a list of packaging requirements for the item. The majority of the items have more than one packaging requirement.
What is the quickest way of merging all the packaging requirements for each item together?
I thought this would be very easy to achieve in Excel but is proving difficult. Basically I have a number of excel worksheets with the headers as below. What I would like to do is to use the staff number from each worksheet to populate and sum a master worksheet. Some staff numbers will be in each of the workbooks while some are only in one. While the employee name should also be the same in each of the workbooks there are some differences in spelling etc.
Mon - Fri Mon - Sat Saturday Sunday/Public Hols OVERTIME Staff NoEmployee Name6 am - 11 am5pm - 12 Midnight12 Midnight - 8am8am - 12 Midnight8am Sun - 8am Mon
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU() ' ' CopyICUCAPU Macro ' Dim i As Integer
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data - a header - a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data - a header - the repeating model number
What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.
I would like to do now would be to only copy the rows that also contain the word sold in column C. I guess that I could do this in two stages, first copying the rows based on the year then copy again based on the word sold in column C. It would be nicer to make a slight modification to my code and do it all in one step.
I have a spreadsheet that has columns stating both status and then further to the right in the row, currency totals. There are only two status options, Stocked and On Order. I need to add the total currency amounts based on this other column's listing and it must change if status changes.
(Put simpler: Column A lists Stocked or On Order and Column B is the currency tied up in that row. I need to make totals for the cells in column B based on the status listed in Column A and it must change when Column A is changed. This should result in two totals, one for Stock Status Currency and one for On Order Curreny)
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.
Is it possible to place formulas in vba to control cells based on the cells contents.
A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat
What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#
if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
I am just learning VBA. I need to move to a cell location that is named in a range. As follows:
The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like:
Application.GoTo Range("Sheet2!A1")
I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.
I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.
What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE] ClassAB 1032 1511 2012 [/TABLE] Can this be achieved through a formula?
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.
The setup *I have a folder called Zircon Plant *That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders) *The Register is located in 03. Documentation *The PDFs i want to hyperlink to are located in 02. PDFs
What i want to do *i have 2000 rows of drawing names *the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21) *the cells i want to contain hyperlinks are V21 - V2020
so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.
(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)
If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"
based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is
' Select PDF range ActiveSheet.Range("u21").Select ' Promt PDF Hyperlinking ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"
I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets
I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
I have 2 columns that I need to use simultaneously to conclude something.
Column A Row 1: Dog Row 2: Cat Row 3: Bird Row 4: Dog Row 5: Dog Row 6: Dog
Column B Row 1: See Vet Row 2: See Vet Row 3: Get Rest Row 4: Get Rest Row 5: Get Rest Row 6: See Vet
Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
See attached file, basically need a formula that will give me the unique resources for each location. I know a pivot table would be easier but i need a formula for my model to work.
