Merging In Excel Using Unique Identifier
Jun 7, 2014
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
View 6 Replies
ADVERTISEMENT
Jan 24, 2010
does anyone have any thoughts on any way to create a formula that will create a unique identifier for a single row? I thought about using concatenate to combine a few columns in a row which will probably work 99% of the time, but I cant risk it being wrong 1% of the time. Any ideas on how to incorporate checking if the concatenation (aka the unique identifier) already exists, and if so, add a number or something to it to make it unique?
The data has rows added and removed on a normal basis in random spots on the sheet so I cant simply just have a number incrementing for each row I add as the unique identifier. In other words, the unique identifier should never change even if I add or remove rows anywhere in the sheet.
View 9 Replies
View Related
Jan 5, 2006
I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
need to be able to identify each row via a unique identifier, but am having
trouble working out what to do when new rows are added or when existing rows
are copied and paste.
New rows and copied rows should get new unique identifiers, but I can see no
way of doing this. Does anyone have any suggestions? There is nothing unique
about the data that I can hang on to and the "SheetChanged" event does not
fire for inserted rows in Excel 2000.
This spreadsheet is owned by one of our clients, so I can't add new
worksheets or columns to the existing workbook - I can just add cell
comment.
View 14 Replies
View Related
Aug 10, 2007
Here at work we have a set of 12 workbooks, that form an extensive activity plan. I have created an add-in that creates a worksheet menu bar item and adds different functionalities to these 12 files. Because any user might have more than one of these documents open at one time, I am forced to refer to the documents by ActiveWorkbook. A bonus added feature: the names of these files change periodically, but not at the same time.
Before running any procedure I must validate that the active book is one of these files. Currently I am testing the existence of a veryhidden sheet codenamed "shIsValid" in the workbook. Now I'm thinking, there has to be a better way to do this. Simplest way would be if ThisWorkbook object had a Tag property similar to UserForms, but this doesn't seem to be the case. I wonder, is there actually a way to give a workbook any identifier (boolean, string or number) that I can access directly, so I can get rid of these dummy worksheets.
View 3 Replies
View Related
Dec 14, 2011
How would I set up a formula that can search for a unique identifier if the unique identifier is not know.
I would like to set up this formula in Tab 1 where it looks to column A in Tab 2, which is a column of about 600 unique identifiers, to find any cells containing the string "VA". There will be multiple cells that include "VA" in the string
I would like this formula to collect all the cells in the column A in Tab 2 into a column of cells in Tab 1 beginning in Cell A5. I would copy the formula in Cell A5 of Tab 1 down multiple lines. I can add a segment to the formula that will return a blank if there are no more cells with the indicated string included.
Tab 1 formula begins in Cell A5Tab 2 of Unique IdentifiersFormula that will copy first cell in Tab 2 that includes VA in the string from Tab 2VANewsprint23005-2119Formula that will copy next cell in Tab 2 that includes VA in the string from Tab 2TNPackaging37309etcALPackaging35044etcVAPackaging24526-0040etcONPackagingP0K 1E0NHPackaging03743VAPackaging24521NYPackaging13667MIPackaging49099VA
Packaging24426ONPackagingM1P2Y9ONPackagingM1P 2Y9WIPackaging53512-0386MIPackaging49042VAPackaging24504
View 1 Replies
View Related
Dec 8, 2009
I think a macro could probably do this, but I am worthless with VBA. What I need to do is consolidate lines of text if they have the same numeric identifier. See below.
ID Text
345 This is a sentence that
345 has line breaks in it, but
345 needs to be consolidated
345 into one cell, if you would
345 be so kind as to help me.
345 KTHX!
346 Oh no, here's another one
346 that has a different number
346 of rows. Eep!
Not sure how to consolidate text or concatenate it, since it's not always going to be the same number of rows each time this happens. I need something with an if statement and...some other crap.
View 9 Replies
View Related
Jun 15, 2009
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!
As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually
Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)
View 12 Replies
View Related
May 5, 2014
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
View 2 Replies
View Related
Jul 8, 2014
I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
View 3 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
Jun 5, 2013
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?
View 3 Replies
View Related
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.
View 9 Replies
View Related
Jun 13, 2014
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
View 5 Replies
View Related
Apr 15, 2014
Using EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
View 2 Replies
View Related
Aug 6, 2013
I have an excel file which has many rows that are almost duplicate but some columns would differ, I would like to merge them, I will try to explain what I mean by "merge" .
Code :
Sub Remove_Duplicate()
Dim LASTROW As Long Dim I As Long Dim J As Long Dim K As Long Dim MyVALUE As Variant
[Code]....
Since I am very new to this language and my tinkering doesn't work at all. Whether or not a row is "almost duplicate" is based on columns A to G.
View 1 Replies
View Related
Mar 31, 2011
I have a workbook with multiple sheets (21 sheets) in it. The header on each sheet begins at row B5. The actual data starts from row B6. The number of rows in each sheet varies and the last row contains the total for all columns that has numbers. I wish to have one sheet that combines data from all the 21 sheets. But;
1) The header should be repeated only once
2) Only the rows that has data should be included, blank rows should be ignored
3) I do not need the last row i.e. total to be considered while merging the sheets
I've given a snapshot of the data below. The first column contains the row number just for reference.
B5 Name Address Telephone Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Total Other Members B6 Tony PQR Complex Road 1 12345678
300
500
800
B7 Stany PQR Complex Road 1 1234567890
1,000
[Code]...
[IMG]file:///C:/Users/DERICK%7E1.FER/AppData/Local/Temp/moz-screenshot.png[/IMG]
View 8 Replies
View Related
Dec 5, 2013
I have two columns
Req | Expiry Date
yes 11/12/2010
yes 08/06/2012
yes 02/07/2017
how do i merge both columns into one.. not losing any data hence
Req Expiry Date
yes 11/12/2010
yes 08/06/2017
I am using excel 2007 ITS FOR WORK!
View 9 Replies
View Related
Jul 12, 2014
How I can merge multiple word documents based on checkboxes selected on Excel.
Specifically, I have some 75 checkboxes found on Column F with linked cells on Column G (for example, checkbox1 place on Cell F1 linked to Cell G1). Each of the checkboxes have a corresponding Word document (containing text and tables about 1 to 4 pages in length). Based on what checkboxes are selected, I'd like to have those corresponding Word documents merge together and create an entirely new Word file (for sake of order, the datapoints from each selected Word document should be placed at the beginning of a new page in the new file).
The user will eventually name and save the new file on their own.
I'm not familiar with the required code as well as where it should be placed (whether on the excel file that contains the checkboxes, on the individual word documents that are eligible to be merged, or parsed out on all files).
View 1 Replies
View Related
May 2, 2014
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
View 1 Replies
View Related
Jul 8, 2013
I have two workbooks, Quotes and Invoices. They have a common field (column) between them called "QuoteID". Since not all quotes turn into invoices, there are more rows in the Quotes workbook than the Invoices one. Is it possible to match the "QuoteID" cell values in Invoices to just the ones in Quotes that match up, and then merge them into a new worksheet? If not, is there an add-on that will accomplish this?
View 1 Replies
View Related
Jul 7, 2014
Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.
Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079
I want output to be ----->
Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079
macro/formula for the above requirement..?
View 11 Replies
View Related
May 29, 2013
I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.
View 2 Replies
View Related
May 24, 2014
I have 30 xlsx files in a folder and I want the first sheet of all that files to be merged to a new workbook. The thing is I don't want the macro to copy paste the value in to the same sheet of the new master sheet like Ron's excel merge tool does. I want a macro to create new 30 sheets on the master file and copy the data from source files. And I want the newly added sheets to be renamed as the source file name. This works well except the sheet renaming thing.
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code].....
View 1 Replies
View Related
Aug 12, 2014
I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
View 9 Replies
View Related
Feb 16, 2012
We have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..
every job is on its own row and contains a unique ID.
is there a way i can merge their data in to the correct row and column(s) on the master sheet?
i've been playing with datasources etc
Excel 2010
View 2 Replies
View Related
May 13, 2008
when I code VBA .. I am constantly referring to cells like:
Worksheets("").Range("")
or Workbooks("").worksheets("").Range("")
is it possible to do something like
Dim var_1 as Range
var_1 = workbooks("blah").Worksheets("blahagain")
then to use:
msgbox var_1.Range("A1").value
View 9 Replies
View Related
Jul 29, 2012
I'm looking for a code where all those rows which contain some identifier be deleted.
Eg, If any row contains identifiers like "----" , "PROG:" , etc shall be deleted.
NOTE: The identifiers are found on the A Column of any row.
All rows which do not contain the above identifiers shall remain untouched.
View 9 Replies
View Related
Sep 3, 2008
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
View 9 Replies
View Related
Oct 6, 2012
I have a table with an identifier in column A and values in columns B to Z.
The structure is like this:
IdentifierA
value1
value2
value3
value4
IdentifierB
value5
value6
[Code] ......
There is 3.000 rows of data. As you can see some of the identifiers have no values.
On another sheet I need the following table to be generated in this format (two columns):
IdentifierA Value1
IdentifierA Value2
IdentifierA Value3
IdentifierA Value4
IdentifierB Value5
[Code] .....
Note that as IdentifierD has no values it shouldn't be in the new table.
View 3 Replies
View Related
Feb 10, 2010
this formula is on Sheet1 and works ok but would be a LOT better if I could use some variables in it.
=SUMIF('Sheet2'!$D$56:$D$96,Sheet1!G3,'Sheet2'!$E$56:$E$96)
G3 is a date
$d$56 to $d$96 are dates
$e$56 to $e$96 are Dollar amounts
I would like to be able to replace the $d$56 to something like $D
and $d$96 with $D
same with $e...
View 9 Replies
View Related
