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?
View 6 RepliesI am currently working on a script that will copy some data from one sheet to another, but I keep getting the following error message:
Run time error: Object required
at
Set uRng = .Range("F1", .Range("F" & .Rows.Count).End(xlUp))
What could be causing it?
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 have a spreadsheet with unique identifiers.
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
Merging the two sheets by this unique identifier?
I have the need to convert regular emails to a excel sheet,
The emails are always sent from the same email address, and will have the same format ( however can contain small or large amounts of data eg 1 part number or 50 ).
sample email below
" PowerForce v4.00
Report : INR3050 v4.5
Title : EDI PURCHASE ORDER INBOUND ERROR EXCEPTION REPORT
For : ch3inl
Date : 05-Mar-14 06:06
[code]....
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.
View 1 Replies View RelatedHave a long list of patients with visit details recorded - over 3 years worth
Many patients are seen multiple times, there is an age recorded also to ensure the same names can be 'set as distinct for different ages'
Need to replace (or add another column) the name field with a unique identifier so that we can ensure the same person has the same identifier.
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 have been set a task to do and I wonder if you could point me in the right direction.
Task - extract 2000 emails from a 6000 email database
The 2000 emails have to be proportionate to the original database.
e.g.
The main database has the emails plus town and employee size ranges
Column A - Emails Column B - Town Column C - Employees
So if Column B states that 50% of the entire database is from one town, then my extracted emails must also have half from that town (1000).
Also there are around 5 employee ranges and so they need to also be proportionate to those percentages too in the final extraction.
I have an excel file having different columns. The test data is as under
Email HQ City Product Value
S@y.com XYZ BAC abc 10
S@y.com XYZ CAD bcd 20
S@y.com XYZ BAC abc 10
S@y.com XYZ CAD bcd 20
A@b.com XYZ BAC abc 10
A@b.com XYZ CAD bcd 20
A@b.com XYZ BAC abc 10
A@b.com XYZ CAD bcd 20
The above is sample data and the rows will change depending on data.
Based on email id the range should be picked up and the email should be send as a attchment.
I have the office 2007 Suite.I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.
I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc, The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers
Quote:
Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, There are more and i won't list them all. Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.
I don't want to use accounting software as that is too complicated for my simple little business. What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.
At the moment i'm doing all 3 tasks separately, when i know they can be automated.
1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?
2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.
3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.
These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.
I have one sheet that contains three columns of data. I want to look in another sheet and find if any of the rows in that sheet have the same data found in the first sheet.
I have attached an example sheet of what I need done:
In reference to the example, I am first looking in sheet 2. The information from Column A and C is what I want to find in sheet three if it is there. So for example, A1 and C1 are Joe and 233. I want to then look into sheet 3 and see if any row in sheet 3 has Joe in column A and 233 in Column B. if so I want the value in column B (233) returned into a new sheet.
I need this process to repeat for all rows in sheet 2. The number of rows may change in either sheet.
i have is that i want to use data from specific work sheets in another generic sheet, i have a list of names in the generic sheet and a lot of sheets titled by peoples names, i want excel to search for a sheet whose name corrisponds to the name in a cell on the generic sheet and copy from that individuals sheet a specific cell and put it in the generic sheet.
View 11 Replies View RelatedI have created two sheets: "Roles to Courses" and "Datasheet". I have attached the zip db for review - its only 314k. I need to search the Datasheet in columns N, P for values from the Roles to Courses sheet (Business Role - SAP ECC) column A and find a match in the Datasheet. When a match is found, I need the B,C,D,E,F,G,H,I,J,K columns that contains the values of the classes from the Roles to Courses sht, to be added to the field called "Training Courses" in the Datasheet.
I started a macro but wasn't sure enough to complete the commands.
I have two worksheets and I want to match two columns from each worksheet and return me a respond with "yes" or "no" For example:
Sheet 1
Name Item
ABC 123
DEF 456
GHI 789
Sheet 2
Name Item
ABC 123
DEF 789
GHI 134
So, I want to search the "name" column in Sheet 1 with Sheet 2, if they found the name is matching, then search for "item" column in sheet 1 with the "item" in sheet 2. If they both match, I want to return with a "yes" if not return value "no" in a different column #3.
I tried to do it, but it keeps on finding "789" in column 1 with "789" in column even though the name is different, it still return me a value of "yes."
I have been searching through the forums for examples and haven't been able to find exactly what I am looking for.
Ok here is my issue. I need to compare the Conf # and line number on both sheet 1 and 2. So basically if the Conf # and the Line number match between page 1 and 2 then it copies the Conf #, Item Number(Sheet 1), and Detail(Sheet 2) to sheet 3 in a row. This needs to continue till the end of the Sheet 1. I have a pseudo report created with no code because I don't have a clue as to where to start.
I m trying to match the values based on the Coulumn B
[IMG][URL]....
;base64,iVBORw0KGgoAAAANSUhEUgAABVYAAALYCAIAAAAYRj5jAAAgAElEQVR4nMy9Z1RU+Z73Oy/uWs9aM
/eZmTtzn5nT3doGoNLOu3KmCEXOQUURUcCAomIgmXOgDYCK5JyhcoQiQ5HNWcE2dNt9uo8dTve
ZM3PCfbF3RYJo95m5rs+qVb0bpMoO8P3s7+/3/zvrwYOL5cCB4dnk5bmTm2tnKCeHYDAnZzA7m2AgK2tg
/347/fv29e/b1793bx
[Code]...
When you run the below macro from my orders.xls file it opens my template.xls file and writes data to specific locations and saves the file as the order number from my original file orders.xls. Now for single item orders which are located on the 2nd row there is no problem, but for multiple item orders which each item is on its own row it overwrites the first order because the order number is the same.
Here is the macro and in comments is an example of what I'm trying to accomplish.
Sub PackingLists4Protocol()
' Protocol flatfile ProtocolOrders.xls translated and creates files named by order number
' CSV ONLY
' Keyboard Shortcut: Ctrl+P
[Code].....
I'm trying to eliminate any .select or .activate from my code to speed things up and reduce screen flicker. At the moment, I have a sheet created at the click of a button. Basically for every date exclusive of weekends, there are 4 entries, for each date there are the following entries AM Metro, PM Metro, AM Regional, PM Regional. The user wants to be able to change the date range in the "overview" sheet and when there is data already in the other columns for these matching rows, have the input sheet update with old (copied??) info, if no previous data then add all 4 rows and sort... if previous date not in date range then delete entire row.
View 4 Replies View RelatedI am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project. It's a huge puzzle and is now my "baby".
Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)
Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?
Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...
I'm not sure if this is best handled in the Formulas & Functions section or elsewhere, but this is my best guess. I have a spreadsheet used for tracking hardware issues with data in two worksheets:
1. PageCount
Column 1: Date
Column 2: Serial#
Column 3: PageCount
2. Tickets
Column 1: Date
Column 2: Serial#
Column 3: TicketNumber
What I'm trying to do is find a way to extract from those two sheets the date, serial number, page count, and ticket number where the date / serial # are the same and dump this to a new sheet. I've thought that I may need to concatenate the date / serial number together into a single cell or something, but I'm really at a loss as to how to pull this data. Part of me thinks this should be done in SQL, not a spreadsheet, but that's how our reports are produced.
I have two sheet one is copy sheet and other is past sheet. In Paste sheet Header Title is fixed values , copy sheet header title also same but its not proper aligned & few header title not available. I want to transfer entire data from copy sheet to past sheet matching with header title. No need to change paste sheet Header title & alignment order.
Attached File: Data Transfer.xlsx
I have a DATA sheet that is all of the information reported on a monthly basis. This format will never change, but the data will. I have a second sheet called MMUSAGE.
On the MMUSAGE tab, I have identified which headers (in red) from the DATA tab that correspond with the headers in the MMUSAGE tab. Question is: how do I move the data from the DATA tab over to the MMUSAGE tab under the appropriate headers?
Example: IF Product Number on Worksheet(DATA) = Product Code on Worksheet(MMUSAGE) then move all data to Column L.
I have two sheets - one work sheet and the second - database with existing entries. Going by visual example below, I need the column C of the work sheet to automatically populate with the data pulled from the database sheet. For example, C4 on the Work sheet should find both values "Bike" and "Green" in one row on Database sheet and return the corresponding value 44444 from F97. The work sheet cells in the row C should remain empty if no match found on the database sheet.
Sheet 1 (Work sheet)
-------A--------B---------C
1---Auto-----Red
2---Auto----Green
3---Bike-----Red
4---Bike----Green
5---Bike-----Blue
Sheet 2 (Database)
-------A--------B---------F
12---Auto---Red----11111
37---Auto--Green---22222
85---Bike----Red----33333
97---Bike---Green--44444
102--Bike---Blue----55555
Macro: Copy selected cell to another sheet next blank row
I'll just make this simple to understand.
*Again, I have first sheet, named "SUMMARY"
*On Column A, I have list of names
A1: Star
A2: Moon
A3: Sun
A4: Clouds
*these cells corresponds to the Cells of different sheets
A1: Star = E3 of Sheet 1
A2: Moon = E3 of Sheet 2
A3: Sun = E3 of Sheet 3
A4: Clouds = E3 of Sheet 4
this is what I should get:
1. I select the cell A1 as an active cell
2. press the button (with the macro on it)
3. I will automatically go to the sheet where "Star" is located
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies View RelatedI have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...
i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..
I am trying to paste data from one sheet to another using their string but not getting the logic.
below is the example for the same.
Sheet1 - data source
A
B
C
ABC
56.32
45.65
DEF
26.5
23.6
Sheet2 - Data needs to be pasted
A
B
C
DEF
23.65
36.65
XYZ
23.65
12.65
PQR
15.23
25.65
ABC
65.59
65.26
MNL
26.65
23.65
Value contains in Column B and C for ABC & DEF should replaced by the values in Sheet1
I have two worksheets to combine. One of them have list of student numbers and their names, and the other has student number and their grades. I have to make another sheet that shows their name matched with each of their grades(grades are organized by date)
My thought process in this is to match the student numbers in two sheets, then combine name and grades and paste to another sheet.
How can I do this using VBA macro code?