Finding Similar Value From One List In Another List?

Oct 7, 2011

On Sheet 1 I have a list of employee names (John, Bob, Ross etc...)in column A and in column B I have a list of employee bonus points (1, 5, 3 etc...). On Sheet 2 I have the same setup but the list of employees on sheet 2 is a lot longer than the ones on sheet 1, all employee names are on sheet 2.

I need a macro that will go down to each name on the list on sheet 1, column A, and copy the employee bonus points then go to sheet 2 and find the that employees name in the list and paste the bonus points in column B. This must be done until the last name on sheet 1 is found and all points are copied to their corresponding names on sheet 2.

View 3 Replies


ADVERTISEMENT

Finding List Which Has Most Similar Entries To Reference List

Apr 26, 2014

I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list

View 8 Replies View Related

Add To Dropdown Validation List Or Similar?

Jan 29, 2012

Is it possible to part fill a cell with some prepared text from a list or similar, but still allow the user to finish off the sentence?

Using a drop down list in A2, I want to have a drop down list in B2 so the user can select from that list, (B2 list is specific to A2 drop down list)

I want the user to be able to add to that selected entry when they have picked it from the list in B2

IE: from the drop down list in B2 = "To be or not ......" I want the user to be able to finish the quote

View 2 Replies View Related

Search Similar Entries Within A List

Oct 3, 2013

I have a list of entries of which some are distinct and others are similar. For example, I have 1) JohnSmith 2) JhonSmith 3)SomeoneElse. The first and second entry are similar but not equal due to a spelling mistake, the third entry is not related to the first. My question is: How can I obtain which entries belong together? The output would have to show that 1&2 are similar and that 3 is different.

View 4 Replies View Related

Evaluating List Of Similar Names And Assigning Common To Variable?

Sep 17, 2013

I am working on a customer report template that generates our customers reports and will send them out automatically.

This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.

What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.

Here is a quick example of what data we get raw from the system:

Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range

This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.

You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.

So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.

View 9 Replies View Related

Excel 2010 :: How To Extract List Of Titles Having Similar Category

Oct 30, 2013

I have lots of DVDs and decided to catalogue them using Excel 2010. I made use of 3 Sheets. In Sheet 3 there are 2 lists: Category (PG, M18, General, Adult) and Language (English, Chinese, Japanese, Korean, Spanish). Sheet 2 contains the data under the following headings: Title, Category, Language, Genre. I used Sheet 1 to create a drop down list for selecting a Title and then the information of the title are displayed, that is, its category, language and genre (I used vlookup). This is working fine.

Then I created another drop down list for Category. I want to be able to select a category, such as M18, and then a list of titles that fall under this category will be displayed. However, when I tried using vlookup with index, I failed to get the display of titles.

View 1 Replies View Related

Finding Names Within A List

Sep 14, 2009

I am creating an interactive tool in Excel.

One of the Fields is "Employee Name"

I created a validation list that includes the names "Mary, Joe, Michael, and David"

When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.


Passport
Mary
Joe
David

Lunch
Mary
Joe
Michael

Car
Joe
Michael
David

I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.

View 14 Replies View Related

Finding Top 3 Most Common From A List?

Apr 14, 2013

I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.

I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.

I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.

I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.

View 1 Replies View Related

Finding From A List Within Spreadsheet

Nov 1, 2012

I have a spreadsheet with the first 17 tabs containing data and the last tab the 18th one contains a list in col a of 4,000 unique code items.

Is it possible to check if any of the codes items exist in any cell of the first seventeen tabs.

View 1 Replies View Related

Vlookup Not Finding Value In List

Feb 26, 2010

Excel 2002

I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.

View 9 Replies View Related

Finding Similar Numbers In Two Sheets

Jun 25, 2013

I have an excel file which contains two sheets. In one sheet there are some numbers in a single column. In the other sheet again there are certain numbers span across rows and columns (a number may be duplicated). I need to check whether there are numbers which exist in both the sheets. If such a number is found it may be marked (say with a color) in both the sheets.

View 2 Replies View Related

Finding Duplicates Using Countif Or Similar

Jan 13, 2014

I have over 400 thousand rows in my data-set and I'm trying to find duplicates using a countif or similar formula. Let's say I have the following:

Blue
Yellow
Green
Blue
Blue
Yellow
Pink

I need the formula to increment the count. So for example, the first time Blue appears, it equals 1, the second it appears, it equals 2, third time is 3 and so on.

And, I need it to actually work with 400 thousand rows. I have tried using the following formulas without success as Excel keeps crashing:

=IF((COUNTIF(T:T,$T2)>1),COUNTIF(T:T,$T2),"")=IF(COUNTIF(T:T,T2)>1,2,1)

That second one equals "2" if there's more than 1, which will work for me as well. Better would be if it increments though.

The purpose of having a count is so that I can pivot the data, then filter out all the "1"s and report on it. Therefore I can't use conditional formatting, or simply using Excels Remove Duplicates feature. I need the same data-set for other pivots and charts.

View 8 Replies View Related

AverageIf - Finding Similar Text

Sep 26, 2007

I've searched around the web for AverageIf solutions and found the CSE formulas. However I've come across a big problem I can't resolve.

I have two columns of data. Column A has _similar_ text and column B has data.

The problem is the following: AverageIf does not support finding similar text. This means that a formula like:
=AVERAGE(IF(A2:A400="Info/German*";F2:F200;FALSE)) does not work.

Column A contains words like Info/German, Info/German2, Info/Italian etc. But I only need to find the average of cells that begin with "Info/German".

View 9 Replies View Related

Finding The Biggest Value In Custom List?

Apr 26, 2014

I know how to make customs lists, but it's not getting me anywhere.

Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:

5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a

and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.

You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).

So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.

View 14 Replies View Related

Finding And Inserting Value From Previous List?

May 15, 2014

The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example

A B C D
Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0

I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...

A B C D
Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300

As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.

Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...

A B C D
Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300

My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.

View 14 Replies View Related

Finding Closest Date In A List

Apr 9, 2008

I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.

I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.

So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.

What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.

View 9 Replies View Related

Finding The LAST Time Something Appears In A List?

Apr 17, 2009

I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.

I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.

I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!

View 4 Replies View Related

Finding Anagrams In A Word List

Jul 28, 2009

In column A we have words, like this:

CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK

In column B we have another list of words:

NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER

I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.

(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).

In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).

View 10 Replies View Related

Finding List Of Keywords In Column?

Mar 17, 2014

I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?

View 4 Replies View Related

Finding Average Age From List Of Dates?

Jul 20, 2013

Finding an average age from a list of dates. This is just a small sample:

7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013

I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.

View 9 Replies View Related

Finding Data From A List And Deleting

Jun 6, 2007

I have a large keyword phrase list.

In a sheet called "AllKWs".

What I would like to be able to do is find all Country and town names in that list and delete the names only in the phrase, not the whole row.

Several points though.

Can the macro (As I will assign a text button to it).
Only find and delete these words if they are either at the start or end of a phrase?

4 example phrases; ....

View 9 Replies View Related

Finding Keywords On A Large List

Jan 20, 2009

I need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems

1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word

2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.


Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........

View 9 Replies View Related

Finding & Displaying Unique Data Within A List

Oct 6, 2008

Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.

View 13 Replies View Related

Finding 10 Smallest Unique Values In A List

Oct 2, 2008

I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

View 2 Replies View Related

Finding Items In List With Blank Cells?

Dec 5, 2011

I have a list of about 100 cells that's going to have user-inputed data for varying cells that could repeat. For example:

AAA
(blank)
BBB
(blank)
(blank)
AAA
(blank)
CCC
etc.

I need a formula(s) for a second list that can find the non-blank cells and put them in order (not necessarily alphabetical).

View 9 Replies View Related

Finding All Unique Combinations Of List Of Words?

Dec 6, 2011

I have 15 different words that I want to order in as many ways as possible, but only 3 at a time.

So let's say the words are:

cat
dog
elf
clown
monkey
rock
bananas
(etc)

then I want a list that has all of these possible combinations:

cat dog elf
cat dog clown
cat dog monkey
cat dog rock
cat dog bananas
cat elf clown
cat elf monkey
(etc)

until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.

I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.

View 9 Replies View Related

Creating Top Ten List (Finding 10 Most Occurring Values)

Sep 5, 2012

I am trying to create a top ten list of my clients, my clients are listed by customer numbers. The data is listed as follows:

Client ID
7164
7164
7164
5781
5781
6392

So my results should be:

Top 1 7164
Top 2 5781
Top 3 6392

I have been trying to use Mode, but that only returns the top value.

View 2 Replies View Related

How To Categorize List / Column Into Separate Ones By Finding Keywords

Jul 28, 2014

Say I have a list:

VB:
Something something apple
Something something orange
Something something banana
Something apple something
Something banana something
Apples woo
Apples And bananas
Something orange something
Something something apple pie
Something something vegetables

And I want to separate the list into separate columns/lists with certain keywords (e.g. apple, orange, banana) like so:

Apple
Something something apple
Something apple something
Apples woo
Something something apple pie

[Code] .....

The "multiple words found" part isn't a big deal (I'm not sure how I'd deal with it anyways). The "Unsorted" part is there assuming creating the separate lists won't remove the entries from the original list (which would then leave all the unsorted text).

View 3 Replies View Related

Finding The Maximum Number Before A List Turns Negative

May 21, 2009

i have the volume of a reservoir in column A for lets say, 2 years, with monthly data (so 24 numbers in my list)

I also have some variables that feed into this list such as rainfall (fixed), and population (i.e water use, which i can manually change)

if i increase the population too much, the volume of the reservoir will at certain times of the year, fall into negative digits.

what i'm looking for, is a cell which works out the maximum value (in whole numbers) for my population, without any of the numbers in the reservoir volume list falling below zero.

View 9 Replies View Related

Finding The Largest Numerical Component From An Alphnumerical List

Jul 20, 2006

I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.

I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved