The yellow highlight is where you input the data needed. The problem is it doesn't show the result when the letter has a duplicate. For example, when I enter "a" in the first input cell(input letter) and "1" in the next(input number), I get the result("a1"). However when I input "a" in the first and "2" in the second, the result just shows a blank cell, and so forth.
View 9 RepliesI have got a wordlist in worksheet "original" which looks like:
Before: [Code] .....
I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".
After: [Code] ......
The macro must be able to deal with very large lists. biger than 200 000 words in column A
Here is the excel file containing the example : excel file
i have got a wordlist in worksheet "original" which looks like:
Before:
How do I lookup a table , eg. E4:G16 to see if that table contains a data.
I tried {=search(A4,E4:G16)} where A4 is in cell F10 but returns Value error.
I have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.
View 2 Replies View RelatedI have an appointment schedule shaped like a table and I need to extract data from it into a sub table. The schedule has date column headers, time row headers, and the intersection of the date and time headers is a cell with a patient’s name. There are duplicate patient names in the schedule. The extract table should list the patient names vertically and next to each patient name should be a list of dates and times. The time and date for a single appointment should be contained in one cell.
For simplicity, I will not use dates and times in my example, but instead I will use letters.
Schedule looks like this:
Date Column headers are as follows: B1 = m, C1 = n, D1 = o, E1 = p
Time Row headers are as follows: A2 = q, A3 = r, A4 = s, A5 = t, A6 = u, A7 = v
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin
The extract table looks like this:
Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain times and dates are: C21 = “r, p”, C22 = “q, m”, C23 = “r, n”, D23 = “s, n”, E23 = “q, o”, where the first letter is a time and the second letter is a date.
A while back I posted a question about this sort of reverse two way lookup:
[url]
Since then I have encountered the problem of the patients showing up in more than one time slot on a single day and also showing up in the schedule on more than one day. The formulas I am using are not working. So in my example, the trouble I am having comes from the fact that Sioux’s name shows up in more than one time slot for one day and also he shows up on more than one day.
If it is easier to see the table with dates and times, the data is here:
Schedule looks like this:
Date Column headers are as follows: B1 = 1/8/2010, C1 = 1/9/2010, D1 = 1/10/2010, E1 = 1/11/2010
Time Row headers are as follows: A2 = 8:00 AM, A3 = 9:00 AM, A4 = 10:00 AM, A5 = 11:00 AM, A6 = 12:00 PM, A7 = 1:00 PM
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin
The extract table looks like this:
Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain patient names are: C21 = 9:00 AM, 1/11/10, C22 = 8:00 AM, 1/8/10, C23 = 9:00 AM, 1/9/10, D23 = 10:00 AM, 1/9/10, E23 = 8:00 AM, 1/10/10
In my enclosed attachment, you will see my dilemma. I have a list sort based on the cell value, but when two values are the same, it shows the first alphabetical listing twice when I INDEX. I can go into the individual cell and correct it, but how can I apply the formula to all the cells so no matter the matching values, I always get name A and B, rather than two A's, for example?
View 3 Replies View RelatedIs there anyway that the UDF can remove duplicates? It's not a huge problem, but would make things a bit easier.
View 2 Replies View RelatedI have this formula below it will return multiple results is it possilbe to modified it that it will return multiple results but ignore duplicates. I would like it to be a stand alone formula no helper cells or helper columns.
=INDEX(B$2:B$15,SMALL(IF(A$2:A$15=E$2,ROW(A$2:A$15)-ROW($A$2)+1),ROW(A1)))
I have two spreadsheets of data, one column contains an employees name, the other is a value.
Employee Amount
Bob 10
Frank 12
Katie 14
Katie 55
Simon 5.2
I would like to do a lookup that states if name is Katie and value is 55 and is in sheet 1, return "Yes", else return "No"
The name and value conditions will read off the other spreadsheet.
I need to do a sum with multiple criteria and that can account for duplicate entries and select the correct duplicate to add. I have a sheet with 6 columns of data: TripDate, Company, DepartureTime, BookedTravelers, TripNumber, DataEntryDate. It's just a running data dump from multiple sources and times and can therefore have duplicate entries for a specific trip but where the number of people booked has changed and I need the sum to use the most recent number (dataentrydate is newest).
Example:
TripDate Company DepartureTime BookedTravelers TripNumber DataEntryDate
10/31/13 A 15:30 10 111 10/01/13
10/31/13 B 15:30 11 999 10/01/13
10/31/13 C 15:30 12 999 10/01/13
10/31/13 A 16:00 20 222 10/01/13
10/31/13 A 17:00 30 333 10/01/13
10/31/13 A 15:30 50 111 10/15/13
10/31/13 A 15:30 40 111 10/25/13
Currently I have this in a seperate worksheet to break down the hourly loads where A2 is a date reference I can change to what date I need, and each company is listed in a different column with hours running down Column B from 0400-2200:
=SUMIFS(BookedTravelers,TripDate,Template2!$A$2,Company,Template2!J$4,DepartureTime,">=" & $B5,DepartureTime,"
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
col A col B col C
AA 1 dog
BB 2 cat
AA 1 cat
BB 3 cat
The end result is:
AA 1 dog
AA 1 cat
BB 5 cat
In this example there are two examples of AA, but they each have a different animal, so they need to remain separate. BB is listed twice, but has the same animal, so the total for them is 5. I am looking for a way to express this with with a formula. I tried using Sumifs, and it sort of works, but I end up with BB listed twice, and I don't need that to happen....
I have two columns: Column A with SS#, Column B with loan #. i need a formula that would return 2 different loan #s for same SS# on a different worksheet. Example
SS#:Loan #ResultSS#Loan #123567812312356781231235678345123567834534578917862950013351
Spend all morning on this. Tried various INDEX, COUNTIFs, MATCH, VLOOKUPs but when looking at the same SS# in a column it always returns the first Loan#.
I'm adding data from a report into a spread sheet and some of it will be duplicated. I want to remove the duplicate data, but is there a way to differentiate between the older (and more complete) data and the newer data? In other words, how do I get rid of the duplicate while keeping the one I want to keep?
I was considering the advanced filter, but if I create too many columns of criteria will it be seen as unique?
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
This is a follow-up of my previous post which was brilliantly solved. I found out that I need someting additional, which is over my head really.
I know have the following formula: IF(SUMPRODUCT(($G$4:$G$3000=$G6)*($AA$4:$AA$3000=AA6)*($O$4:$O$3000=O6)*($I$4:$I$3000=I6))>1,....
However, I want to change the end into something which can count the number of appearances of the duplications in a specific row, so I would get results saying that they appear 1 time, 2 times, 3 times or 4 times (4 being the most) in the list and could be placed after the text value of O and I (I398&" "&O398,"-"))?
I hope I formulated this well enough....
Also, does anybody have an idea how I can message my file? I've got close to 3000 rows all the way up to AB and my pc now starts to complain...
Below is the criteria I need a formula for.
First Criteria - If column D=John Smith,
Second Criteria - Excluding duplicates in column A, (red are the unique values I need)
Third Criteria - Sum of column B that meets the above criteria first
Result I am after is
John Smith - $154,330.00
James Brown - $150,520.00
David Williams - $161,390.00
A
B
C
D
[code]....
I have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)
Is there an easy way of doing this?
I have a large spreadsheet where I've successfully ranked a large series of data based on revenue, using product classification as a criteria & summarising this on another page with the top 20 products. The problem I'm having is that within certain criteria's I have some products with the exact same revenue figure, so if the 2nd ranked product within classification "C" for example has revenue of $100, & there's another product within the same classification also with $100 of revenue, on my top 20 report I have results for the 1st & 2nd ranked product, but a blank for the third product & then results again for the fourth
The formula I'm using is
=IF(Y12="NA",0,SUMPRODUCT(--(Y12=$Y$12:$Y$6000),--(AC12
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.
Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones
I want to be able to count one number in "A" but only if the name is not duplicated in "B".
My result for #25 would be 2.
My result for #50 would be 1, because Mike Jones is duplicated.
The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
I posted a thread a while ago asking for a formula to rank based on multiple criteria (Rank On Multiple Criteria). The following; = SUMPRODUCT(--($A$2:$A$50=$A2),--($B$2:$B$50=$B2),--($D$2:$D$50=$D2),--($G2>$G$2:$G$50))+1)
works an absoulte treat, however is there a way to modify it so that does not allow duplicates? The data in the G column refers to time values acheived during testing of athletes, the problem arises when two athletes achieve the same score and thus they are ranked the same. However, body mass is also measured (F column) and in essence the athlete with the lower body mass has achived the better score and thus should be ranked accordingly.
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I would need an excel formula not macro
I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....
I have duplicate product ids on same date with different unit sold and need to extract all the unit sold for a product id on a particular date. How to concatenate a formula? The array formula that I use does not work:
=IF(MIN(IF(UnitsSoldOnlineVlookup!$A$2:$A$980=A2,
IF(UnitsSoldOnlineVlookup!$C$2:$C$980=B2,
ROW(UnitsSoldOnlineVlookup!$A$2:$A$980)-
[Code] .....
The formula needs to lookup units sold in UnitssoldOnlineVlookup tab and enter them in Units sold column (highlighted in orange in the attached UnitsSold workbook) in Performance tab.
UnitsSold Workbook.xls
I have attached a workbook with 2 worksheets Refund & Factor. In brief I wish to work out the refund on a ticket. To do this I have entered the start date & surrender date. In C29 I have a formula that gives the datediff in months & days. I then have some VBA that extracts the numbers and enters them into D29 & E29.
I now need a formula to look at the factor sheet and find the intersection between 3 (months) & 19 (days) which is 13.96 and copy this to B36 on the refund sheet.
Currently using Lookup(2,1/...) to match 2 criteria before giving me my end result. It works perfectly. I'm now needing to match 3 criteria... is there an easy way to modify this to allow it? My Current formula looks like
=LOOKUP(2,1/(Upload!$D$1:$D$25&":"&Upload!$F$1:$F$25="Gordon Brown "&":Downing"),Upload!$I$1:$I$25)
And as well as matching Gordon Brown, Downing, I'd like to match the word Motor which it would search in Column E of the Upload sheet.
i am having problem with two-way lookup criteria.I have a chart for permissible weight as per height of a person.But there is another criteria of age-e.g.col A (A8:A25)has the various height-groups(from156cm to 190cm) and row 7 (B7:I7) has the age-groups(from 15 years to 48 years) .All figures of weight are within B8:I25.Now if i want to get the permissible weight of a person whose height and age are known ,how do i get using the lookup function?
View 3 Replies View RelatedI need some VBA help for my project...
attached is my sample spreadsheet...
I need the excel to be program so that when i input the 'Grade' and 'T', it will automatically select the py value.
Say i select the Grade S275, and input T= 10.4, thus excel will look up at the S275 table, then to the T< 16 (because T=10.4) and thus select '275' and input it at cell E7.
if i put input Grade S355 and input T=43, then it will look up at the S355 table, then to T<63, and then select '335' and inout at cell E7