Identify, List, And Sum 2 Columns
Apr 14, 2009
It's a time sheet that I need to have whatever data is listed in column E to be listed starting in cell C27 but only once even if it's listed several times. In cell D27 I need the sums to match the data to it's left with the values from above in column D. I've used fill colors to help show my intent. I would also like it to keep track of remaining vacation time by recognizing the word 'VAC' from column E and subtract the value from it's left. This value is shown in cell J26 as '46hrs REMAINING'.
View 4 Replies
ADVERTISEMENT
Jul 12, 2008
Have a list of approx. 125 people (for our church's prayer list). What I would like to do is have a formula/macro that will identify and print the ones on the list
-that have been on the list less than 2 months
-identify by printing one "*" by thier name when the 2 months will be up in 2 weeks
-identify by printing two "**" by the name when the person will be removed in one week.
Another way of stating what I am wanting Excel's functions to do is the following:
In one column, I would enter the date a person is added to the list, then I would want the formula/macro to calculate the date the person would be removed from the list. When the list is printed weekly for our bulletin, I would like the program to create the list/print only the persons who have been on the list less than two months. In addition, I would like for the ones that will be removed in 2 weeks to be identified with an asterick when the printing is done. Then 2 astericks when the person would be removed in one week.(This would allert people to update us on the person's condition and continued need for special prayer.)
Of course, I would like this calculation to be done automatically when the data base is opened. OR REQESTED. I'm not sure of my terminology here! I want the calculations to be done without me having to redo the database and formula/macro weekly.
I sure hope this request is clear!
View 13 Replies
View Related
Oct 13, 2006
I need to identify duplicates in a list and have the foilowing formula:
=IF( COUNTIF(range1,A2)>1,"Duplicate","")
This works ok but i have a further condition which i dont know how to factor into the formula. I think i could write some vba to determine the dupes but i was hoping to avoid this as im sure it will take me an hour or so. Duplicates are identified at the moment as being identical numbers in column "amount", i now need to specify duplicates as being identical numbers in this range where there is at least one row with no pay date filled in in col "paydate"
View 2 Replies
View Related
Jun 1, 2012
I have two columns of B2 and C2 data in the same worksheet and want to identify duplicates in the columns and return a value of true or false in column D. The formula I have been utilizing is:
=IF(ISNA(VLOOKUP(B2,$C$2:$C$5000,2,FALSE)),0,VLOOKUP(B2,$C$2:$C$5000,2,FALSE))
However, i receive a #REF# return in D. What am I missing ?
View 6 Replies
View Related
Apr 9, 2014
I have a macro-routine that generates a table of all sheet-headers (+/- 100-120 sheets) in a workbook. It's crusial that all headers - row("1:1") - have the same values in respektiv columns -so I have this routine to check that this really is the case, before new prosess. The table is rather huge - so I should som way or another identify the columns with more than one value (f.ex back-ground-color in column-top-cell). In this case I must edit and correct the header, and run my routine once again - until all headers are the same
Table is plased in Sheetc("HeaderCheck")
My routine works, but I dont know how to fix this mark-up stuff.
View 3 Replies
View Related
Jan 12, 2010
I have a list of objects:
A
B
C
D
X
D
A
F
G
H
I know how to get a unique list. How do I identify those that are in the list more than once and how many times it is in the list?
View 2 Replies
View Related
Nov 2, 2006
In an excel file I have 2 lists of files. List #1 has about 6,000 filenames and List #2 is a subset of List #1.
I am looking for a solution which will help me highlight/identify those files which are also in list#2.
Ofcourse one way is to sort both lists and then place them in consecutive columns (say column A and column B) and then in another column (column C) use a IF function to identify if A1=B1 (or A1 <> B1) and then take it from there. However this approach helps a litle becuase there may be a gap in List#2 and thus for a great # of cells in column C i will end up getting false answer. Hope you're following me??
The underlying requirement for this is rather lenghty so I'm skipping those details. However I would like to share that so far and whenever I come across doing this exercise (about 3-4 times a month), I ended up achieving the results but it takes me hours.
View 9 Replies
View Related
May 18, 2007
The following code takes the selected item from a listbox and populates another listbox with the item. The problem is that only the item from column 1 is populated to the other listbox. How can I populate the other listbox with the items from both column 1 and column 2?
Private Sub cmdSelToExc_Click()
Dim i As Long
With Me.lstProducts
' Copy all selected items, then delete.
For i = 0 To .ListCount - 1
If .Selected(i) Then
Me.lstExclude.AddItem .List(i)
End If
Next j
Next i
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
.RemoveItem i
End If
Next i
End With
End Sub
View 2 Replies
View Related
Mar 14, 2008
I have two spreadsheet in which I am looking for duplicate customer names.
1st - "Customers"
2nd - "Orders"
In column A of my Customers spreadsheet I have the following formula
=IF(ISERROR(MATCH(E2,Orders$D2:D1000,0))=FALSE, "Y","N"). This allows me to see if a name in my Customer spreadsheet (column E is a field of names) is also in my Orders spreadsheet (column D is a field of names). My end results is either a Y indicating a duplicate or a N for no duplicates.
What I am looking to do next is to place in column B of my Customers spreadsheet the date that coincides with the duplicating customer name. Sorry to be confusing. If a customers name is found in the Orders spreadsheet I need to know what date has been given to this customer (column L in the Orders spreadsheet contains the date information).
However, on numerous occasions a customers name is found in the Orders spreadsheet more than once meaning there may be more than one date which needs to be placed in column B of my Customers spreadsheet.
View 9 Replies
View Related
Mar 19, 2008
What is the syntax that I have to use to identify whether or not an entire column or a row is blank? Specifcally I want some code where I can say if row 1 is null, or blank then delete the entire row or. if column A is blank then delete entire column
View 2 Replies
View Related
Jun 21, 2007
I regularly import a list of data into Excel containing employee team names, employee names, and (for these purposes, irrelevant) data. The team name is the only data in column I, and therefore I can easily pick this up an summarise it in a lookup. The employee name is always one row above it, and in column D. However, it is not the only data in column D, and each employee's records contain varying numbers of rows. How can I have Excel copy the employee name in to column J, alongside the team name in column I, but ONLY in rows where there is data in column I?
View 3 Replies
View Related
Jul 9, 2013
I am working on a project, I have three different lists that I want to compare and I want to identify in 3 separate columns which titles are found on which list.
For example:
Title List 1List 2List 3
ACAROLOGIA x
ACCIDENT ANALYSIS AND PREVENTION x
ACROCEPHALUS x
ACS CHEMICAL BIOLOGY x x x
ACS CHEMICAL NEUROSCIENCE x x x
ACS COMBINATORIAL SCIENCE x x
ACS MEDICINAL CHEMISTRY LETTERS x x
ACS SYNTHETIC BIOLOGY x x x
View 4 Replies
View Related
Jul 10, 2013
So here I have two columns Column 1 with repetitive values of some 10000 records Column 2 with unique values of 100 records I want to compare Column 1 and Column 2, identify the values in Column 1 that match with Column 2 and highlight them. Conditional formatting for duplicate values doesnt work because Column 1 has repetitive values.
View 6 Replies
View Related
Feb 24, 2014
- I have a list of 8 digit numbers in Column A sorted small to large
- Some of the 8 digit numbers are duplicated.
- In Column B I would like to have more of a combination of countif and listing the duplicates in order (1 for the first dup in the column, 2 for the 2nd, etc.)
Example of what I'd like to see
Column A Column B
123456781
123456782
132546841
685036541
985413561
985413562
985413563
As you can see if the number in column A is on the list one time column B would show a 1If the number is shown two times (the first time it shows up will show a 1 the second will show a 2) If the number is shown 3 times (the first time it shows up will show a 1 the second will show a 2, the third tiem would show a 3)
View 7 Replies
View Related
May 30, 2013
I have a pivot table and perhaps that isnt the best way to do this. (Sample attached). Ultimately I need to display two columns only:
Application (ColA) Business (ColC)
There are three columns of data.
Applications (ColA) Usage(ColB) Business (ColC)
There are multiple instances of the same application in ColA, ColB has usage =true or blank and ColC has multiple instances of the same business.
Based on the count of Application in ColA, and the count of Usage =True, I need to return the Business with the MAX count.
Note: the usage cannot be a filter, it must be a count.
View 1 Replies
View Related
Apr 27, 2014
I have data (e.g. PPL0106AU) in columns A (Rows 2 to 104) & C (Rows 2 to 303) and need to identify which entries are in BOTH columns.
I've entered in column B: =IF(MATCH(A2,C$2:C$303),A2,"No")
This formula is resulting in a duplicate of column A in column B.
View 7 Replies
View Related
Feb 19, 2013
I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).
To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).
View 3 Replies
View Related
Sep 2, 2013
I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:
[Code] .........
For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.
What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)
View 1 Replies
View Related
Nov 20, 2013
I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:
Character 1 Character 2 Character 3
Species A 1 0 1
Species B 3 2 2
Species C 1 2 0
I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.
View 14 Replies
View Related
Apr 18, 2014
I have in column A and B. A includes two (or may be more) kind of items, i.e. drink and snack. Column B has for example water next to drink and chips next to snack. The list is huge.
How can I generate a drop down list with data which uses the A column to identify the items which it should include. I want to make two separate drop down lists for drink and snack and each should include only those options which belong to those categories. I.e. drink list should be like water, cola, pepsi, etc. The problem is that the list is huge and it's not possible to sort it. Some kind of if statement structure with named range? The drop down should be dynamic in a sense that if I add line to data, it should be picked to the right drop down menu.
View 9 Replies
View Related
Feb 18, 2014
I need a macro to identify the largest value across columns I2 - AS2. Once found, I need to replace the value with value found minus the value in BQ. The final step would be to highlight the cell that was changed across columns I2 - AS2, then move to the next row and do same until I reach the last row in the worksheet. The number of rows vary each day but the columns do not.
I have written the attached code, which identifies the largest value in the first row, but I don't know how to replace the value found with the new value and then move to the next row until it reaches the final row for the day.
In the attached file, my largest value sits in AC2. That value needs to be replaced with 486.17 (1,175.37 - the value in BQ2 of 689.20). The cell should then be highlighted so I can quickly identified what cells have been changed.
View 3 Replies
View Related
Nov 20, 2009
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View 4 Replies
View Related
Jun 11, 2013
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
View 7 Replies
View Related
Apr 17, 2013
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
View 3 Replies
View Related
Oct 15, 2013
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
View 4 Replies
View Related
Dec 17, 2012
how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
View 6 Replies
View Related
Mar 3, 2014
It's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
View 2 Replies
View Related
Mar 17, 2009
I have highlighted 6 cells.
1) basically, i want to get the FMV from a FMV database tab. I would like to have it able to get the latest value available.
if i use if-then function, i could only have 7 arguments, and it would be very troublesome
2) after getting the FMV, i would like the FMV date cell be able to return the date of the FMV from FMV database e.g. Q1 2008/ Q2 2008/
View 8 Replies
View Related
Mar 20, 2013
I have a huge list of part numbers that has component part numbers listed as well. I am trying to take the list and make a list of Parent part number and identify the component part numbers that are used in all the parent part numbers. For example from the table below of the three parent part numbers there are only two parent part numbers have a component part in common 0011 and 0015 .I am looking for a fast way to create a list that looks like the example table two below.Parent Part Number
component Part Number
1000123
0011
1000123
0012
1000123
0013
[code]....
View 9 Replies
View Related
Aug 19, 2008
Finding the differences in letters (symbols) between 2 columns eg
column 1 column 2 column 3 should show
bidu aapl aapl
ldk bidu ldk
spwr rimm spwr
rimm
View 8 Replies
View Related
