Use Values From One List To Remove Rows From Another List

Feb 24, 2007

I have two lists in the same workbook:

List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.

List 2) Contains account numbers of customers who wish to be removed from the first list.

I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):

Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
Sheets(" Deleted List").Select

'ActiveSheet.Next.Select
Range("A1").Select

Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select..................

View 3 Replies


ADVERTISEMENT

Remove List Values Not Present In Another List

Dec 5, 2007

I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.

I want to remove all workstation numbers and their information which are not present on the first list from the second list.

In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).

In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.

View 9 Replies View Related

How To Remove Duplicate Values Of Last Name List

Apr 24, 2014

So for a project I need to put together a list of Portuguese-speaking last names. So I have various sources where I copied from, so I have one column in excel where I put all that data. I sorted the data to be in the correct alphabetical order. But when I try to remove the duplicate values in that column is says it removed them but it didn't and when it does it leaves many behind. I have tried the trim function, and even formatting but nothing works. I attached the non-formatted and non-trimmed list, because it doesn't seem to be making a difference.

View 3 Replies View Related

Can't Remove Duplicate Rows From A Combined List

Aug 4, 2013

I have 2 lists, A and B. The lists were Obtained by different methods. However the information is now static and It seems that they have compatible information and format. But when I cut and paste A to B to make a single list, and I try to delete duplicate rows in the single list, I'm unable to do so. What can I do? Attached are the lists.

View 8 Replies View Related

Multiple Combo Box Reference List But Remove Used Values

Aug 8, 2013

i have a list of 5 teams in a named range ("teams") within a worksheet.

On a userform I have 5 combo boxes.

What I would like to have is an easy way to remove a used name in the list for the next combo box.

i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.

i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.

only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.

View 2 Replies View Related

Remove Items From DV List Once Used / Display Elsewhere / Reset List When Filling Across

Sep 13, 2013

It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.

Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.

On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.

I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.

This is about as far as I've managed to get with it.

I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.

I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.

So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.

I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.

scheduledemo.xlsx

View 1 Replies View Related

Remove Entries From List In Comparison To Another List?

Oct 28, 2012

Ok, so lets say i have one list of names (cell:value)

A1:Value 1
A2:Value 2
....
An:Value n

and create a new list

F1:Value 1
F2:Value 2
....
Fn:Value n

I want to have a list nearby and i'd like somehow for a function that will remove entries that already exist on the first list.

View 4 Replies View Related

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 Replies View Related

Named List: Data Validation To Restrict The User To Only Selecting Values In A List

Jun 15, 2007

I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.

Insert > Name > Create
Name: Fruit

Refers to:
banana,apple,orange

When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.

View 4 Replies View Related

List 3 Different Values To List Of List Of 3 In Combobox?

Jan 12, 2014

how I can list 3 different values to a list of list of 3 in a combo box, is.

combo box

select high = 35
select middle 30
select low = 25

I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection

View 9 Replies View Related

Obtaining List Of Unique Values From List Based On Some Criteria?

May 28, 2014

The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?

Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String

[Code]....

View 2 Replies View Related

Macro To Delete Rows That Have Cells That Don't Contain Values From A List

Mar 7, 2014

Each day I am going to have a list of about 300 different ID numbers, which i have already got a macro for creating, that outputs them into a single column on a sheet.

The next challenge Ill face now, is that I'll have a list of maybe 500 ID numbers in another spreadsheet.... I need a way to basically tell excel to keep rows that contain the numbers in 1 column on the list of 500, that correspond with those on the list of 300.

Numbers that are not found on the list of 300 ID numbers, must be deleted, along with the entire row.

I essentially need a macro that runs something like this that i found online, but instead of it just looking for the word "apple" as rows to delete, i would need it to check to see if the number is one of the 300 on my list.... and delete the row if it is not on the list

PHP Code: 

Sub Delete_Rows()Dim rng As Range, cell As Range, del As RangeSet rng = Intersect(Range("A1:C20"), 
ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "Apple" _ThenIf del Is Nothing ThenSet del = cell
Else: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume Nextdel.EntireRow.DeleteEnd Sub 

View 3 Replies View Related

List Values From Multiple Columns In A Dropdown List

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

Remove Names Without Values From A List Of Names And Values

Mar 1, 2013

I have the list below and would like to create a new list which contains only names with corresponding values and lists them.

+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10

The solution should look like:

+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10

View 2 Replies View Related

Drop Down List Which Returns Values Based On What Has Been Selected In The Previous Drop Down List In The Adjacent Cell

Mar 19, 2009

I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.

View 2 Replies View Related

List Duplicate List Values

Aug 24, 2008

I have an excel spreadsheet that is several thousand entries long. There are cases in which duplicate entries will spring up on that list.

What I want to do is come up with a formula that will find the first duplicate entry in that list and then display that entry in cell on another worksheet. If it was at all possible, I would also like to have the row that the duplicate entry is contained on display as well, in a different cell of course.

I do not want to highlight them using conditional formatting since I have used that to perform other tasks on that sheet and that wouldn't display the entries in a different cell for me, and I would prefer not to use a macro to perform this function it that was at all avoidable.

View 7 Replies View Related

Drop Down List Which Displays A Different Set Of Values Depending Upon The Value Selected By A Previous Drop Down List

Oct 26, 2009

I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .

View 4 Replies View Related

Remove Duplicate In List

Sep 4, 2013

I used to have a macro that did this for me, but the layout of the exported data has changed and now the whole macro has had to be rebuilt.

I've got a list of two pieces of data: Material (Column A) and Order (Column B). I'm putting a spreadsheet together and populating it with VLookUps. This spreadsheet will list the materials and the Orders after each, but then I also want to list the second Order in the next cell if there is a second order.

For instance, in the below, Material 10880873 has two different Orders. The first VLookUp grabs Order 513792568. I need the second VLookUp to grab the next Order 513792569.

MaterialOrder
12012223513571068
12149080513739571
10880873513792568
10880873513792569
12215347513792570
12160123513801190
12171779513802721

View 6 Replies View Related

Remove The 0 Results From A List?

May 6, 2014

I have to print invoices by order number and product. The way I have done this creates blank spaces on my invoice sheet. If I could remove the 0 results without deleting the row it would bring all parts of the invoice together so there were no gaps.

View 1 Replies View Related

Remove Blanks From A List

Jul 10, 2009

Data is in a horizantal list, say row 2:

A2: SAM
B2:
C2: MARY
D2: JOE
E2:
F2: DAViD

i.e. like this:

SAM-blank-MARY-JOE-blank-DAVID

I am looking for formulas (not VBA) to compact this list by removing the blanks so the new will be placed in K2, L2, M2 etc. as follows:

SAM-MARY-JOE-DAVID

This is a good article but only works on vertical lists:
[url]

View 12 Replies View Related

Remove String From A List In Col

Jan 22, 2010

i would like to remove string "Total" from a list in col A,

Sheet1 AB5How do I remove total Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Remove Blanks From List

Oct 24, 2006

I need to generate a list of items from a column of data which frequently changes. In the attached example, I need to list all entries other than those that are blank (NB - the data generates the numbers as text and the blanks as "").

View 8 Replies View Related

Look Up List Values Between Two Values; List Row Contents

Feb 24, 2009

Sheet2 contains various data, including one column of dates.

What I would like to do is populate Sheet1 with functions that search Sheet2 for date values between two separate date values on Sheet1 (cells F1 and G1). When a match is found on Sheet2, row contents from Sheet2 should populate on Sheet1.

In addition, I would like the functions only to match listings in which the value in column E on Sheet2 is greater than zero.

I have attached a sample workbook that illustrates the structure of the worksheets and the desired results. Looking forward to finding a good solution.

View 5 Replies View Related

Formula To Remove Duplicates From A List?

Apr 3, 2014

I have a list of what we call "model codes" which are is a similar format to "DFS41FC57DD728NCWRY3"

The list could be 6000 rows but may contain hundreds of duplicates and may only contain 50 different model codes

I am looking for a formula that will populate a new column (B in the example below) with only the individual codes (unfortunately the xls example I created wont upload)

COLUMN A COLUMN B
DFS41FC57DD728N3SRY3 DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3 DFS41FC57DD728NCWRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728NCWRY3

View 5 Replies View Related

Remove Blank Cells From A List

Nov 13, 2006

Is there a way to remove blank cells from a list?

Example:

A
1

3
4

5
5
Result:
1
3
4
5
5

View 9 Replies View Related

Remove Blank Cells From List

Nov 14, 2006

I need to remove blank cells from a list of cells. I need to do this through a formula or a macro. I can't use Filter or Sort. Example:

A
1

5
5

2
3

Needs to look like:
1
5
5
2
3

View 2 Replies View Related

Remove ALL Non-Unique Entries From List

Mar 6, 2008

I have a list in Row A that has duplicates. I want to be able to delete both entries (itself and the duplicate). When done I want the list to display only be entries that are unique or better said any entries that never had a duplicate.

View 8 Replies View Related

Remove Item From Validation List After Selected?

Feb 10, 2012

I am a softball coach and I want to create a fielding roster for my team. I have all the players names in column A (A4:A14). I have positions listed in column L (L4:L15) as follows: P, C, 1B, 2B, SS, 3B, LF, LC, CF, RC, RF, X. Cell range B4:H14 (7 innings) is where I need to have drop downs, but once I select a certain positions for one player in inning 1, for ex., I want it to not be available for the next player in the same inning.

View 6 Replies View Related

Array Formula To Remove Duplicates From List

Apr 10, 2010

I'm looking for array formula that will condense a long list like this:

a
a
a
b
c
b
d

To this

a
b
c
d

View 5 Replies View Related

Using VBA To Find And Remove Multiple Instances From A List?

May 20, 2014

I have an input sheet with 3 columns: Currency pair, Tenors, and Value.

Like this:

EURUSD 1m 3
EURUSD 3m 0
EURUSD 1y 5
GBPUSD 1w 2
GBPUSD 1m 9
GBPUSD 2m 1

etc etc for about 100+ rows.

This input is exported from a system we have and may vary in length and values from time to time.

What I want to do is pull all the data for specific currency pairs (probably for 5 of the 10+ currency pairs it displays) onto another worksheet. And for each tenor for that currency pair, i want to perform 10-20 lines of analysis on the next sheet.

So, if GBPUSD was a currency pair I was interested in, I would need 10-20 lines for GBPUSD 1w, 10-20 lines for GBPUSD 1m, 10-20 lines for GBPUSD 2m, etc.

How can I pull this? While maintaining flexibility so if the tenors, currencies, and # of lines change?

View 1 Replies View Related







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