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?
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 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.
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 Replies View RelatedHow can you tell what a column number as referenced by the column alpha identifiers.
Ex: Column AN would equal what column no?
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies View RelatedI'm trying to write a macro to use as an auto-update feature for a spreadsheet.
I currently use Data validation on an input cell to allow the user to select from a list of product types.
I'd like the update to affect this list, so new product types are always available to the user.
However, the list of product types is in another spreadsheet and contains numerous repetitions.
I've managed to get the following code together based on what I've used before and what I've found searching so far, but it seems to have a bug in it as it wont always work. I also need the original copying of the list to be done to either the new spreadsheet of a different page in the original spreadsheet, but every time I try this it causes an object error.
PHP
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 05/12/2008 by Information Technology
'
Workbooks.Open Filename:= _
"T:SSTCCDEngineeringBackthin_dataPhotolithMASK_DETAILS.xls"
Sheets("Mask List").Range("f4:f2000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("a1:a3"), CopyToRange:=Range("E1:E2000"), Unique:= _
True
Selection.Sort Key1:=Range("E1:e2000"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("E:E").Select
Selection.Copy
Windows("MASK_DETAILS.xls").Activate
ActiveWindow.Close
Windows("Dry_etcher_log_B.xls").Activate
Sheets("Calc Sheet").Select
Columns("M:M").Select
ActiveSheet.Paste
End Sub
I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature
Is there a way to export Excel (Excel 2003) into a flat file that is comma separated and also includes text identifiers, specifically double quotes (" ") around each field in a row?
The Export Wizard in Access makes that a snap but we (more than just me) haven't been able to make it work out of Excel.
I have a problem with a spreadsheet my colleague at work has designed. He has entered all the names into it as Mrs A Jones, Mr P Smith, Mrs S Pilkington-Smyth etc. I need to sort these into Alpha order using just the surname. macro or a formula to count backwards until I get a space and then strip this off to allow me to sort it.
View 2 Replies View RelatedI have a single page spreadsheet. I want to print multiple copies and have a unique invoice number on each page printed in cell O1. I don't want to just send it to the printer as individual print jobs. I would like to send it to print as a pdf in a single 100 page document or if not a pdf then just as a multiple page document.
For example first print run would start at number 1001 and last numbered page is 1101.
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies View RelatedHow would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.
View 2 Replies View RelatedI would need to count unique frequencies in column A, but only for a specified value in column B.
Example:
A|B
1|1
1|1
1|1
2|1
2|1
2|2
2|2
3|2
3|2
I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies View RelatedI have a worksheet with Coulmn A and B , wherein , Column A has a list of values that can be duplicated. Based on the values in Column A, I want to fill corresponding cells in Column B. see the attached.
way to accomplish the same. Tried VLOOKUP.
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
How do I count unique values in Column A only if the values in Column B match?
A B
1 a
2 a
1 a
4 b
5 b
1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
I was wondering if it is possible to hide a column(s) but still have data in that column be visible at the bottom of the spreadsheet. For example, if I hide column G can it only hide the column from rows 1 - 50 and then the column will appear after row 50?
View 3 Replies View RelatedI know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
I have for input one column A with different values:
1
2
3
2
I nedd for output one column B with unique values from column A:
1
2
3
How can I do that?
I am trying to 'tag' new part number records in a spreadsheet (see SS2 attached) - checking against a second excel which contains existing part numbers (see SS1)
If Part Number in SS2.xls (column B) appears in Item Code in SS1.xls (column B) then i would like to put a 'Y' or similar in column J of SS2.xls .
Need to find a way to flag the unique number in column with 641892
2057988-0380121611AAAAA########70002057988-0380121611641892########59262057988-0380121611642176########53442057988-0380121611646570########63032057988-0380121611645812########56232057988-0380121611643573########55912057988-0380121611640502########62482057988-0380121611645723########58592057988-0380121611645818########61662057988-0380121611641295########55712057988-0380121611643574########61452057988-0380121611640079########63042057988-0380121611640450########61252057988-0380121611645494########57392057988-0380121611644573########51832057988-0380121611643595########51842057988-0380121611645404########56442057988-0380121611646669########57812057988-0380121611646890########63052057988-0380121611646013########63022057988-0380121611646755########51822057988-0380121611642278########52732057988-0380121611641892########79262057988-0380121611640787########51802057988-0380121611640774########5426
I have a worksheet called 'DR manager and deceased' which is part of a larger workbook. It contains around 20000 entries. There should be at least 2 rows for each entry, identified by the customer erefrence in column H, however a casting a quick eye down the data shows me that there are several entries where there is only one row for a specific customer reference and I need to identify these. I need a macro that will remove the entire row from this worksheet and copy it to the 'unique' worksheet if the value in column H for that row is unique, ie only found once in the whole of column H.
View 4 Replies View Related
