Remove Rows With Non Matching Numbers
Aug 17, 2007
I have a large file with many item numbers that run down column B. Here is how it is set up. It has a header that says store one. Under store one it lists all item numbers in that store. Then under that it says store 2 and list all item numbers in that store and so on. There are a total of 90 stores with an average of 40 items per store. I have certain item numbers that I want to see but I want to delete all other numbers. Is there a way I can tell it to Delete all items except for the following, and then list the numbers I want it to keep?
View 3 Replies
ADVERTISEMENT
Sep 11, 2008
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies
View Related
Jan 5, 2010
I have 4 Columns. Column A has a master list of inventory numbers(about 3000 of them). Column B has dollar figures associated with them. Column C has some inventory numbers in it(800 of them), all of which are also in the master list in Column A. Column D has dollar figures that are associated with the inventory numbers in Column C.
My question is how can I make it so that if an inventory number in Column C equals one in Column A that the dollar figure associated with the inventory number in Column C gets put into its own column on the same row as the inventory number it was equal to in Column A.
I've tried basic IF functions and I can't get it to work because the inventory numbers aren't lined up on the same row. So one may be on row 300 but the other maybe located in row 12. Maybe if a double sort was possible or if someone knows how to make the row in the IF statement a variable.
View 2 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Aug 8, 2009
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
View 5 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 Replies
View Related
Jun 16, 2009
I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.
Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.
View 4 Replies
View Related
Nov 29, 2012
I am trying to find a function which will do the trial and error bit for me. I have for example 5 numbers an want to fit the answer to the puzzle.
For instance
3 5 X
2 3 Y
1 2 4
Is there a way to find "X"? Then as a result "Y". I can't just go back up the equation as it is not hat simple. Is there a way excel will try an find what fits?
View 1 Replies
View Related
Nov 9, 2007
I have 10 separate columns with an unlimited (but right now 354 rows) and I would like to search for two matching numbers going row by row, giving me the answer in one field that would say how many rows the two matching numbers are in, is there a way?
View 9 Replies
View Related
Jan 14, 2010
I have a very large file with two columns with about 40,000 rows, is there an excel formula to match the same numbers in column A to the same numbers in column B and all the other non matching numbers to be pushed to the bottom of the columns.
View 9 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
Dec 13, 2006
I have 2 workbooks.
The first one (Top Ten Auto Generator.xls) ( Sheet is Summary) has 1 row (13) with 4 cells that have data. A13 with date (today formula), B13, D13, and E13 are numbers. The cell range will be the same each time the data is copied (the workbook has a marco to generate the numbers each day already).
The date doesn't need to be copied, just the other 3 cells data (in the same order) using paste special.
The workbook that needs the cell data is a report ( Dashboard.xls) that has lots of different departments each using 3 columns for their specific data. The left most column A is the date listing to match.
The column Range for my department on that "Raw Data" sheet is "H", "I", & "J" where the "H" would get the data in "B13", "I" would get "D13", "J" would get "E13" for the date that matches the other workbook.
So the way it would work, is that once the vba is run the 3 cells from the auto generator are copied, then the vba opens the dashboard.xls and looks for the date in column A which matches the other workbooks A13 date value, and then the cells are pasted into that row, but in column H, I, & J.
Here's sort of some code that I put together to see if that would be easier to understand. I'm new at doing the vba so I don't have better code.
Private Sub CommandButtonpaste2dash_Click()
'get our data from generator
Windows("Top Ten Auto Generator.xls").Activate
Sheets("Summary").Select
Range("B13,D13,E13").Select
Range("E13").Activate
Selection.Copy
Workbooks.Open "Dashboard.xls"
Sheets("Raw Data").Activate
'look for the date in column A which matches A13 in the Auto generator
Some Code For finding the right date
'once found, paste special to the same row, but in column H (the 3 cells should paste together ok)
some code For pasting into H In the same row As the found date
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
ActiveWorkbook.Save
MsgBox "Done!!!"
End Sub
View 9 Replies
View Related
Aug 28, 2007
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
View 2 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
Jun 17, 2012
I have the following columns (O & S):
S O
-----
1 3
3 7
6 6
8 4
1 3
2 8
1 6
I want a code (Formula) do the follwoing: Matching number 1 in column S with number 3 in Column O and count how many matches found in the two columns, Which in this case only two.
View 9 Replies
View Related
Sep 30, 2013
I have two tables. One has long list of numbers, and the other one has lesser. I'd like to match the identical numbers in both tables. By the way the first table has 14600 cells and the short one has 1466 cells.
1424771
1424782
1424772
1424786
1424773
[code].....
View 9 Replies
View Related
Apr 29, 2007
i have a userform in excel that the user enters a form number into a textbox. As these forms come in a book of 25 i need the user to have the option of entering a 'Start" and "End" number into two textboxes - this i can handle.
What i'm having problems with is working out the code that would make the following happen
When they click the update button i need for the form numbers to match up with the original ones already in a sheet e.g in column "C" is the list of original form numbers. (These are in groups of 25). When we have the copies returned i need to be able to "match" these against the ones listed in column "C". the matched numbers would appear in column "F". most of the time the copies arrive back in "book" lots yet occasionally we may just get a few back at a time.
View 9 Replies
View Related
Sep 1, 2009
In Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
View 2 Replies
View Related
Apr 17, 2009
If a red number to the right shows up anywhere on the left black numbers then I want a total in column G. I want it to automatically total the matching numbers. How can I do this?
View 3 Replies
View Related
Apr 23, 2014
I have a number in column "E" T13245v1
I have a formula in column "D" =RIGHT(E1459, 1)
Which gives me "1" in column "D" (last number in string)
In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.
What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.
View 2 Replies
View Related
Jun 28, 2014
I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.
Period
Cost Center
Account
Item No
PO No
Amount
Test
DEC-12
5000AF
000
M0002648
13579
(59.89)
5.11
[code]....
The formula I used in the test column
=SUMPRODUCT(--($B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13=B2&C2&D2&E2),$F$2:$F$13)
As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?
My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy
View 4 Replies
View Related
Aug 5, 2013
I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.
Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1
View 7 Replies
View Related
Nov 19, 2007
I have a fairly large spread sheet where I need to count the number of instances where the number in one column matches the number adjacent to it in a second column.
View 9 Replies
View Related
Oct 7, 2009
I've been racking my puny brain for an hour trying to figure this out. Basically, my problem is with the last 2 invoices listed. If one invoice has "PO CLOSED" on it, I want all other invoices on that same PO to say the same thing. Surely I can do this with a formula or conditional formatting, but I cannot think of how.
PO #Invoice #DateInvoice TotalDept.ActivityElementPO AmountPO Amount RemainingPO Status0707955289651/12/09$812.9075105854035$2,960.28$59.380707955459806/15/09$667.2475105854035$2,960.28$59.380707956486827/7/09$812.9075105854035$2,960.28$59.380707955584669/29/09$607.8675105854035$2,960.28$59.380719750913336/18/09$947.7975105853305$5,000.00$3,117.210719750915917/17/09$935.0075105853305$5,000.00$3,117.21PO CLOSED
View 9 Replies
View Related
Apr 30, 2008
I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.
View 2 Replies
View Related
May 19, 2014
I have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
View 2 Replies
View Related
Apr 16, 2012
I am looking for a macro that will combine qty's of item codes when first 6 out of 9 numbers in the item number match. Please see example of data below. In this case, the macro looking at the first 6 numbers in used and new. I should get a total of 26 - new and 45 -used.
A B C D E
Item Name yard Item Numb Inv New Inv Used
Frami panel 3x6 416-100 588801000 6 27
Frami panel 3x6 416-150 588801000 10
Frami Xlife 3x6 416-100 588801500 20 8
View 1 Replies
View Related
Jan 29, 2014
I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.
I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.
View 12 Replies
View Related
Sep 10, 2009
there is a none VBA way to remove everything from a cell but the numbers.
Example: A, DAVID (002081) becomes 002081
The cell the formula will go in is F4 and the cell its looking at is K4.
View 9 Replies
View Related
