Find And Replace On Some Duplicates In Huge Data File

Mar 6, 2014

I have a file with 300k lines of SKU data. This is data by month for 12 months (so roughly 25K SKUs per month, with changes in SKUs each month.) I take this data and pivot it and drop it into a file.

I noticed last month when I prepared that the SKUs have names next to them, and sometimes the information is pull by the name instead of the SKU. Unfortunately, in the master data, someone entered different names in a few of the months. So for example, in some months SKU 0012 is Green B and in other months it's Green Beans. I need the names to be the same.

Is there a quick way to fix this given that I'll have 12 records and maybe 3 will be one name and 9 will be there other? Finding and replacing every one is a chore because on pivot there are 40k. I don't think I can do a quick remove duplicates because it would remove multiple months. I just want each SKU to have the same name.

View 3 Replies


Find And Replace Data From File Import?

Mar 24, 2014

.Find and Replace data

I have a file that I keep importing into Excel that shows up in Column A. The Range typically varies to how short or how long the end of the file is. But every time I import a file in, the last 20 rows are the same. The 20 rows I want to replace with other values on another sheet.
This is a simple example:



I would want to replace the numbers with the letters. The biggest thing is if I can filter or have excel find the end of the file (using a shift+crtl+End) or something and have a formula on sheet2 that it can reference; then replace. Any way to have excel automatically find the end of the file and replace values without manually doing it.

View 2 Replies View Related

Huge Data File Delete All Rows With X Value?

Jul 21, 2014

I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).

View 6 Replies View Related

Selecting And Then Importing Data From Huge Tab Text File To Excel

Apr 11, 2012

I wanted to see if there is a VBA code to do the following :

a) Select a TabText Delimited file based on a criteria
b) Import the Selected Data to Excel

I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.

Here is my current query :

Sub TEST()
' TEST Macro

Workbooks.OpenText Filename:="E:Testdata.txt"
ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="1234"
Range(Selection, Selection.End(xlToRight)).Select

[Code] .......

View 3 Replies View Related

Excel 2003 :: Find Data From Huge Database Of Items Of Equipment

May 23, 2012

Macro to find data from a huge database of items of equipment and find certain ones relevant to an area. I am using Excel 2003.

I have a spreadsheet which has three tabs.

Tab one has a list of equipment with a Ref (Col A), ID (Col B), mile (Col C) and chain (Col D) start and a mile (Col E) and chain (Col F) finish.

There are about 25 different Refs and IDs, but all rows have different Mile and Chains.

The second tab is an identical template of the third tab which is where I want the results to go (see below)

The third tab is raw data which list thousands of items but I want the macro to find the items which are in the first tab using the ref, ID, mile and chain information.

View 3 Replies View Related

How To Find Duplicates In Big Database File

Feb 7, 2014

Sorting duplicates. In a big database

column A consist of 2000 names
column G consist of 2050 names
column M consist of 2020 names

How to identify duplicates using vlookup ?

View 14 Replies View Related

Find And Replace A String In Text File Using VBA?

Jan 17, 2012

I have a query in a text file.

Select * from
table1 where
a = %d
and b in ('%d','%d')

and values in excel:

I want to open the file and replace the first %d with value1 and second %d with value2 and third %d with value3.

So finally my output should be:
Select * from
table1 where
a = value1
and b in ('value2','value3')

View 9 Replies View Related

File That Is 50mb And I Don't Know Why It Is That Huge

Jan 14, 2005

I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open?

There is 3 tabs and very little data. How can it be so huge?

View 9 Replies View Related

With Sheet Background, File Size Is Huge

Apr 1, 2007

Is there anyway to put a background on a worksheet, without the file size of the workbook becoming monstrously huge? I have a small jpg file of our company logo, and I think that it would be a nice touch for it to be the background to our spreadsheet. But when I use this 50k file as the background, the workbook suddenly grows to a size of 7MB.

View 4 Replies View Related

Excel Macro To Output Text File After Global Find And Replace

May 19, 2014

See attached image.

So what I am looking for is to put a macro behind button 1 which will do the following

Enter A Default Value Of 123 In The Yellow Boxes (B7:K7)
If They Are Blank When The User Clicks The Button
Performs A Search And Replace To Replace The Values 01-01-1990 With B8, 02-01-1990 With C8 Etc Until K8
Output The Contents Of Sheet 2 To A Text File (In XAI Format) In The Following Order A1:A100, B1:B100, C1:C100

[Code] .........

View 1 Replies View Related

Find Duplicates + Copy Data

Jan 3, 2010

Let's imagine I have many informations about 'Domains' such as Emails, phones, etc. My lists are long and it happens very often that I have the same domain (row) many times. No way to remember each time if I already filled the informations for this specific domain before.

THat's why I want to know if there is a way that ALL the informaitons on the Row of : Domain A will automatically be copied to another row where it will find another Domain A

The Conditional formatting (highlight duplicates) do part of the job by telling me where are the duplicates, but i need the other infos to be copied.

View 9 Replies View Related

How To Find And Replace Range Of Data

Jul 12, 2014

How to find and replace a range of values. I've looked online, and searched the forum with no success.

I have a list of cells in a column labled "Check #1", "Check #2", "Check #3" ect. I'm trying to build a Macro that would replace any value from "Check #1" to "Check #50,000" with "Outgoing Check". I know this can be done manually with relative ease, but it will need to be done very very very often, so I'd like to automatic it as much as possible.

How to put the steps into a Macro or Formula that could be re-produced with ease.

View 6 Replies View Related

Find & Replace With Column Data

May 30, 2009

I was wondering if it is possible to perform a find and replace which would replace data from another column. We have a lot of data fields of our inventory that we exported which we are trying to condense for another program.

Example: I have a description column (AV) that has [[Manufacturer]] within the description. We want to find [[Manufacturer]] and replace with our data column (DD).

This is the data that has our manufacturers name in it. We have several find and replaces to do within the description column besides the above field.

View 9 Replies View Related

Find & Replace Data Between 2 Sheets

Jan 30, 2008

I have in sheet1:

Item QTY
A 1
B 10
C 0

In sheet2 I have

Item QTY

View 6 Replies View Related

Find And Replace Data In A Range Of Cells Only?

Jan 4, 2014

I am looking to Find and Replace (via ctr-F) but I need to replace it only within a range of selected cells. Each time I try to do this it replaces everything even though I have tried selecting each cell and replacing only that. Is there a way to select a certain amount of cells and replace?

View 2 Replies View Related

Find & Replace Data With Multiple If Range

Oct 27, 2009

say that i have alist of data in "A2:A", which are a string file (text) and i want to rename each data to a certain name that defined in range "B1:B2" (String file too).

For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w

& The target name is to be:

For case above, i need to replace C2&C3 in to 16E, while C4&C5 to be 18W.

View 8 Replies View Related

Excel Script - Find And Replace Data

Oct 16, 2013

I am looking to make an excel script that can find and replace data.


So the macro would look at the data in a cell in column B, then look at the adjacent cell in column C, and replace all instances of that data in column A with what if found in C. So the results would be:


But that's not all, I would like it to not change cells in A that already have been changed once! (I'm trying this with changing the background colour)

I am at a complete loss and need to find and replace over 3000 items.

View 4 Replies View Related

Excel 2003 :: Find And Replace Missing The Ability To Replace Values?

Feb 5, 2009

This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.

I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.

Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.

View 6 Replies View Related

Find Values On One Tab And Replace Data From Adjacent Cell

Apr 3, 2014

I have one sheet in my workbook called 'mapping' which has a list of codes on it, the length of which will vary on a periodic basis.

I have another fairly large sheet on the workbook called 'data' (around 2000 rows) that will also vary in size. I want to do a search on the data tab for each code that is contained on column 4 of the mapping tab and if the code is found, enters the corresponding value from column 1 of the mapping tab to the cell 6 columns to the left of where the code was found on the data tab (cols H and B in this case).

The issue I have is the codes maybe contained more than once on the data tab so I need the find/replace command to search the whole of the data tab and perform the task each time.

View 2 Replies View Related

How To Go End Of Huge Data Range

Mar 31, 2014

use CTRL+ Navigation Arrow Key on keyboard i.e. if you wanna go to the last Non-Blank row in a column press CTRL+Down Arrow key if your range contains empty cells then use this shortcut SHIFT+CTRL+Arrow key

if you wanna Get the Value of cell in a column you can use this formula


This formula will work only if there is no Blank Cell in the range.

View 1 Replies View Related

Find And Replace To Not Replace Characters Found As Wildcards?

Jul 2, 2013

I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.

I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?

View 4 Replies View Related

Doing Find / Replace Not Working If Replace Text Is Too Large

Feb 5, 2014

[Code] .....

Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.

I need it to post a message exactly as long as what I have in there. How do I get it to work?

View 6 Replies View Related

Catching Errors When Using The Find / Replace Function / Replace All

Apr 20, 2006

Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.

View 3 Replies View Related

Working With Huge Data But Getting Hanged

Apr 27, 2009

When i'm working with huge data on excel say for exam 50000 records and about 25 to 30 columns, everytime i use any formaula and while saving it the file is not responding, what ever the formula like vlookup, countif, converting in values applying filters and removing filters and with that whatever the changes i do that is not seen next time when i open file.

View 9 Replies View Related

Extracting Top 10 Customers From Huge Amount Of Data

Apr 12, 2013

The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.

The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.

View 14 Replies View Related

Getting Data From Huge Table To Smaller And Organized One

Oct 24, 2013

I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.

Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).

The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....

I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.

The file is attached : Example.xlsx‎

View 4 Replies View Related

Replace Duplicates With Whitespace

Dec 20, 2007

I have a spreadsheet that is generated by vbscript that the first column looks like the text below.

Maint Supplies & Equip
Maint Supplies & Equip
Repairs to Paving
Repairs to Paving
Repairs to Roof
Repairs to Roof
Repairs to HVAC
Repairs to HVAC
Repairs to HVAC

I would like to have it look like:

Maint Supplies & Equip

Repairs to Paving

Repairs to Roof

Repairs to HVAC

Putting blank cells in the place of the duplicated cells. I figured out how to delete the dups but that will not work for my purpose.

View 14 Replies View Related

Find, Replace With And Then Replace Adjacent Cell

Mar 18, 2009

I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.

View 2 Replies View Related

Nearest Number Match - Huge Data To Compare

Aug 2, 2006

i have two columns in tow seperate workbooks to be compared:

in say inp.xls, there is a column(COL A) containting a lot of entries(numbers) - roughly around 60,000. Now in the other workbook, say mem.xls, i have a smaller set of numbers in a colum(COL B) - roughly around 300.

Now, the problem is, COL B, has numbers which may or may not match with numbers in COL A.

* If there is a match(between COL A and COL B), the row number at the match in COL A.

* If there is no match, the row of the closest number (in COL A) before to the number in COL B.


ROW COL A-----> COL B---------->
1 2184058252 2184276560
2 2184058280
3 2184058296
4 2184058312
5 2184058320
6 2184773640
7 2184774216
8 2184774272
9 2184774424

Now, i want to find 2184276560 in COLA, since it is not there, i want it to return the row number of the closest(previous) viz. row 5(2184058320).

Now, since this is a huge comparision set, im wondering what is the best way to do it. Im using VBA....

View 9 Replies View Related

Huge Table Of Dates, Find Range Of Dates

Aug 16, 2006

I have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.

Dates To Test 14/08/2026 19/08/2026









i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D

View 3 Replies View Related

Copyrights 2005-15, All rights reserved