Finding And Replacing Multiple Values?
Jun 14, 2014
I'm currently doing a project which requires me to find and replace a large number of values.
ie. Column A Row 1 = 80099, Column A Row 2 = 554441, Column A Row 3 = Z5478, CAR4 = B89877 etc...
I need to replace these numbers with meaningful values and I have a reference table to tell me what these codes mean ie. 80099 = apple juice, 554441 = orange puree, Z5478 = pumpkin soup, B89877 = banana smoothie. The reference table is in a separate excel spreadsheet and is tabulated in two columns ie. Column A = "the code", Column B = "the value".
There are like 1000+ codes I need to find and replace which I know is going to kill me if I did it manually. How do I find and replace all the values?
View 14 Replies
ADVERTISEMENT
Jan 14, 2013
I have two pairs of columns, A B and E F.
Column A contains one numeric value in each row. Column B lists one person's name in each row.
Column E contains a distinct list of the names found in column B (no repeats), with one name in each row. Each row of column F contains one alpha ID that belongs to the adjacent name in column E.
I need to replace each name in column B with its respective alpha ID from column F, effectively pairing each numeric value in column A with an alpha ID from column F.
Have I stated my goal clearly enough? I could Find/Replace a few hundred times, but I thought there might be a better way using an Excel formula. I'm a beginner-intermediate user, and I clearly don't know enough about Excel to even ask about specific functions for this task.
View 1 Replies
View Related
Feb 20, 2009
It might be a simple question but ive been trying for over a week now and i just cant do it (probably cos my knowledge of VB is useless )
What i need to do is:
I have 2 worksheets: worksheet 1 Articles and worksheet 2 New Prices.
Whats common on both worksheets is Reference Nº (In the case of worksheet 1 its column E and in worksheet 2 its B), what i need to do is replace the price corresponding to reference nº in worksheet 2 (In column E) with the price of the corresponding reference number in worksheet 1 (in column N).
Its a basic find and replace, but its a list of over 17.000 articles so the best way is VB, so i look through your lists and helps, but i just cant do it.
View 9 Replies
View Related
Jan 5, 2014
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
{=INDEX(Jan!$B:$B,SMALL(IF(Jan!$A:$A=Smith!$A$2,ROW(Jan!$A:$A)),ROW(1:1)))}
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
View 3 Replies
View Related
Apr 18, 2008
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
View 14 Replies
View Related
May 27, 2014
Actually i have to two sheets. In (1st file) I have the data of customers. And in (2nd file) I get the approved sales. So i have to find the numbers.
In my sales sheet, there is data of customers like name, address, phone number etc.
In my sales report sheet, i got multiple numbers in a notepad file which are rejected sales.
So basically, i have to copy every number one by one, and then have to find it in my excel sheet with CTRL+F and then i change the color of that cell which is rejected.
I want magic by which i simply add all the rejected numbers in one column of my file, and then do Abra Cadabra by which all the numbers which are rejected get in red colour automatically in the column in which there are all the numbers (approved as well as rejected).
View 13 Replies
View Related
Jan 4, 2013
I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.
Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.
Trial Upload.xlsx‎
View 3 Replies
View Related
Jan 24, 2014
For my job I have to take hundreds of codes and compare them to other codes. For example, in column A I'll have 453 codes, in column B I'll have 352 and in column 97. I want to find common codes for all three columns. Sometimes, I'll have just two columns and sometimes it's multiple columns. I have tried a few formulas but nothing works that well. Any formulas or MACRO
View 6 Replies
View Related
May 21, 2007
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
View 9 Replies
View Related
Mar 28, 2014
I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.
My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.
View 9 Replies
View Related
Apr 14, 2009
I have a file with some statistics on how agents at work performed. This file gets sent to all of them, but before we send it we change the names for a number, so that they don't know how the other agents are doing (avoiding comparisons). People had been doing this manually, but it's getting really complex as the number of agents is increasing. I know that this can be automatized, but I have no idea on how to do it. A simple formula with an IF statement wouldn't be sufficient. Here's an example of the data. The file that is to be modified to be sent to all the agents looks like this:
View 3 Replies
View Related
Jun 8, 2009
I have a spreadsheet with around 1000 rows on data. in each row of data there is a number e.g. 2673 (all in their own cells) so in column A you would have a list of just numbers in another workbook, the number thats in each row corresponds to another value e.g. 2673 = CE103. So this workbook has 2 columns. In column "A" you have the numbers(2673) and in column "B" you have what that number matches with (CE103) i need a way of replacing all the numbers with what they equal
Workbook 1
A B C
2673 XXX XXX
513 XXX XXX
5107 XXX XXX
604 XXX XXX
Workbook 2
A B
2673 CE107
513 CDR57
5107 QV906
604 MNT57
There is an example of what i have (hope its understandable) and what i need is, the data from workbook 2 put in to workbook 1. So instead of having 2673 in workbook1, i would have CE107. And the same for the others e.g 513 would be CDR57.
View 4 Replies
View Related
Nov 8, 2012
I have a data starting from column D. And the list goes like below:
D1: TO
E1: CC
F1: BCC
G1: Body
H1: Text1
I1: Text2
J1: Text3
I would like to obtain the final body in column K after replacing the text with column H, I, J.
Below is how my data looks and How i would like to obtain the final body.
To
CC
BCC
Body
Text1
Text2
Text3
Final Body
View 1 Replies
View Related
May 2, 2012
So I have sheet with various values.
I have a rather big XML type column where I want to replace values (in green) with thier corresponding cell values. Im pretty sure this can be done but have tried with no success!
item>TEST POST http://domain.com/Good StuffCan Be ListedLike ThisOkay StuffCan Be ListedLike This
Bad StuffCan Be ListedLike This]]> 102011-07-07 19:41:16 2011-07-07 19:41:16openopengrand-canyonpublish 00post0
_edit_lastkeya:6:{s:4:"zoom";s:2:"14";s:8:"latitude";s:8:"36.34313";s:9:"longitude";s:10:"-112.51339";s:11:"address_one";s:12:"Grand Canyon";s:11:"address_two";s:12:"Williams, AZ";s:6:"bg_img";s:0:"";}]]>_thumbnail_id
View 3 Replies
View Related
May 29, 2014
I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.
Capture.jpg
I am also attaching the excel file.
View 5 Replies
View Related
May 18, 2014
I am creating a template for formatting data that I download from a wholesaler to upload to a platform. The large database of around 1300 records that comes with category and subcategory names, among all the rest of the data, that I download There are more column titles and information than I need, so my template will have blank headings that I will know to simply delete after I paste. Maybe there is a Macro for that too.
The category and subcategory names to appear in certain columns. I want those names to appear in multiple columns within the same records. How to create a formula/macro to copy all of the data in one column to a different column with a different heading and keep those headings different and save them within my template. I have a list of the 240 with CategoryID, ParentID, Category and Subcategory all corresponding. These always come and go in CSV format, so I cannot create additional sheets to save within my template without a hassle.
In two ID number value columns, I would like all of the text names replaced with corresponding numbers for upload to the platform that requires them. I have been trying to figure out the best way to create a macro to save within my template for daily use, but so far I have not been able to figure it out. I have been watching videos on vlookup, index & match, find & replace...I have not seen what I need just yet
OK, SO HERE IS MY PRODUCT TEMPLATE SO FAR: [URL]
View 13 Replies
View Related
Feb 13, 2007
I have a column titled PA that has several cells with numbers, like 141, 151, 161, 242, 251, 382 etc. Is there a way that I can replace all these numbers in the column using a vba code. For example, replacing all the numbers beginning with 1** to 1 and 2** to 2.
View 3 Replies
View Related
May 23, 2014
I wonder is there a way in excel to replace multiple words at once. To have like script where I had all words that need to be replaced and words replacing those. And just click the button and done? More specifically I'm translating some exports in xls and there are few words repeating over and over again in every document. So for example I need to replace word parfem for perfume like 500 times in one excel document atd. I think there has to be a easier way to replace those words at once.
View 5 Replies
View Related
May 30, 2008
I have a column of 3 letter codes (Righthand side) all differant ie BHP, CBA, NAB, WBC etc. (2000 of them) On the left I have the formula ie =MisData!VOLUME!BHP.ASX
Each formula should have the relevant code within it such as the BHP in the formula on the 2nd row should be CBA, the third formula should have NAB in it. right down the whole 2000 codes without doing it one by one. Now using the find & replace tool to do it one ny one
=MisData!VOLUME!BHP.ASX BHP
=MisData!VOLUME!BHP.ASX CBA
=MisData!VOLUME!BHP.ASX NAB
=MisData!VOLUME!BHP.ASX WBC
View 9 Replies
View Related
Jan 6, 2014
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
View 4 Replies
View Related
Feb 16, 2006
I have a cell that is looking up values in another sheet using HLOOKUP.... when it doesn't find a value it returns a #N/A value.... I have tried using an IF statement with an imbedded ISNA or ISERROR or ERROR.TYPE function to have it return a zero in the event of #N/A and the actual value if it is able to find what it is looking up.... I need to convert the #N/As to zeros so I can use the sum function by merely highlighting a range with my mouse...
View 11 Replies
View Related
Nov 7, 2013
I have a large data set of alphanumeric values (e.g. WUGTI56G36B879568). I need to remove the last six digits from each value and replace them with "XXXXXX". I've already learned that I can insert a column next to my data set and use this formula =LEFT(A1,LEN(A1)-6)&"XXXXXX" to remove the last six digits and replace them with my preferred text. However, now that I've done that is there any way to paste the new values from Column B into Column A without having the formula get all screwy? I don't necessarily want to hide Row A, but instead get rid of it and make Column B into Column A.
View 1 Replies
View Related
Jan 31, 2012
Here's an example of what I need..
I want to
A B
2.5 corn
4 corn
7 green
2 green
I'd like a macro to find all corns, add up the column A for each corn and place the sum beside the last corn in column C
Then do the same with green, etc.
View 2 Replies
View Related
May 11, 2009
I have 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."
This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.
1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.
2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.
I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...
The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.
If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error.
View 6 Replies
View Related
Apr 21, 2014
I have done this before, but it has been a while since Ihave had to use VBA code. What I want to do is find and replace every occurrenceof a test string within a formula with a text string in the first column ofthat row. In the example (I have omitted the formula and left just the word toreplace). I used to have a simple VBAMacro, but not sure what I did with it!
Bob David David David --Every Davidreplace with BOB
Tom David David David --Every Davidreplace with TOM
Fred David David David --Every Davidreplace with FRED
Matt David David David --Every Davidreplace with Matt
View 5 Replies
View Related
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx‎
View 4 Replies
View Related
Apr 29, 2014
Basically what I need is to find a row (I'm doing it by matching the row title that is 'BALANCE'), then I need to find the max value in this row (the problem I'm having is that the row number is not constant), copy that value in another cell and copy the name, that is located in the same column, next to the previous copied value.
View 2 Replies
View Related
Feb 5, 2009
I have a spreadsheet in which one column I'm trying to find a value in another table that has 3 columns (A, B, C). I need to find all the values in A, that fulfill the parameters of less than 1.5 in B, and great than 5% in C.
They all need to be listed in column H while skipping the records that do not meet this criteria.
View 9 Replies
View Related
Feb 18, 2013
I have people who wrote down that they attended a location on a certain date at a certain time, and I have their start and end time.
So for example my person data has something like this:
Arrive: 1/1/12 7:00 AM,
Leave: 1/1/12 10:00 AM
Arrive: 1/1/12 3:00 PM
Leave: 1/1/12 4:00 PM
Arrive: 1/4/12 8:00 PM
Leave: 1/4/12 10:15 PM
I also have those fields formatted as text and as decimals so I can work with whatever I need.
The next portion is a list of events that occur at these same locations. Here are some examples.
Event 1
Start: 1/1/12 8:15 AM
End: 1/1/12 12:15 PM
Event 2
Start: 1/4/12 3:00 PM
End 1/4/12 8:30 PM
What I need to find out is if the person was present at the location during any of the events.
A person may write down more than one visit to the location in a day. The events only happen once per day. I'm sure there's a pretty manual way to do this, however the list of people and events spans an entire year and it will be too time consuming to do it that way.
View 2 Replies
View Related
Apr 29, 2013
I have a graph which is reading from a table. This graph is reading flows from a flow meter, but after reading data for over an hour the graph gets really squished. I'm looking at creating another graph with just the last 50~ values from one column. Is there a way I can create another table that only reads the last 50 values from one column?
View 6 Replies
View Related
