Finding Multiple Values At Once?
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
ADVERTISEMENT
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
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
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
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
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
Aug 19, 2013
I have multiple columns / rows of data, some of which are duplicates.
Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.
I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.
So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.
View 3 Replies
View Related
May 9, 2014
I have been trying to set up a macro to find the Minimum and Maximum values from an array of temperatures for painting... so far, partially successful.
The problems I am having are that the values have 1 decimal place and can be anywhere between 22.7 to -1.4. When they come they are put on the spreadsheet, the MinTemp can be 10.0 and MaxTemp 9.9, totally reversed.
Dim Info(2000, 2) As String
Dim MonthValue(31, 9) As Variant
Dim Working1 As Date
Dim I As Integer
Dim J As Integer
Dim Tot As Integer
[code]....
View 2 Replies
View Related
May 22, 2008
Here is a little formula I working on:
=IF(MID(A1, LEN(A1),1)="i", "x", "")
As it is, it would return an X if the last value of a string is "i"
What I want to really do is check if the last value is a " qoute but it seems you can't put """ or you get an error -- example: =IF(MID(A1, LEN(A1),1)=""", "x", "")
View 9 Replies
View Related
Jul 13, 2006
I have a worksheet where column A is a list of numbers and Row 1 is a different list of numbers
Is there a way to activate a cell given the column A value and Row 1 value? (so I can then insert a value)
View 9 Replies
View Related
Mar 27, 2007
I'd like to know if exist a procedure/ function to find a argument in an array and return the index/address of the argument at the array.
For example, I have a array contaning a list of products. i'd to create a function that finds the index of a particular/list of products, feeded as arguments, and returns the indexes locations.
View 9 Replies
View Related
Jul 3, 2014
I am trying to create a cell that show the value of all specified rows with specific name.
For example:
Apples $2 $5 $10
Oranges $5 $10 $50
Apples $5 $ 6 $ 60
(each of these will be in their own cells, 3 rows and 4 columns)
How do I create a cell that will sum the total value of all the Apples by using Apples as the variable?
View 1 Replies
View Related
Jun 16, 2009
pick a column to test in, this column should be one that will have #N/A error displayed in it and that 'goes as far down the sheet as you need to examine for the #N/A conditions although not all entries have to be #N/A just something in them to the end Using column E for this example as E was where I put a VLOOKUP() formula to test/generate #N/A errors. Const testColumn = "E" change as required
'no other changes to make
Sub DeleteNARows()
Dim naRowList As String
Dim anyRange As Range
Dim anyCellEntry As Range
Set anyRange = ActiveSheet.Range(testColumn & "1:" & _
ActiveSheet.Range(testColumn & _
Rows.Count).End(xlUp).Address)
For Each anyCellEntry In anyRange
If anyCellEntry.Text = "#N/A" Then
naRowList = naRowList & anyCellEntry.Row & _....................
View 2 Replies
View Related
Feb 2, 2006
I have a row of data with the cells I want to find a MAX for are separated from each other:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
View 14 Replies
View Related
Oct 26, 2008
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
View 2 Replies
View Related
Feb 13, 2009
I have a little table:
Columns A:B (separated by commas)
Row 1- Year One, 20
Row 2- Year One, 20
Row 3- Year One, 10
Row 4- Year One, 30
Row 5- Year One, 20
Row 6- Year One, 20
Row 7- Year Two, 10
Row 8- Year Two, 20
Row 9- Year Two, 20
Row 10- Year Two, 10
Row 11- Year Two, 20
Row 12- Year Two, 40
In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).
View 3 Replies
View Related
Oct 11, 2011
I have in Sheet 1 a list of product codes (that should be only numbers, i.e: 1010) on column A, product description on B and a sales column on C.
On Sheet 2 I have on column A I have in the same cell a letter "C" the product code and the product description(i.e.: C1010 Onions). On column B I got the sales total. ( I dont care about the letter "C" it can be deleted)
I would like to use a VBA code to find the same product codes between sheet 1 & 2 and copy the sales total from sheet 2 to sheet 1 when the product code is the same.
View 1 Replies
View Related
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 Replies
View Related
Dec 20, 2012
Code:
Sub DeleteCells()
Dim ws As Worksheet
Dim cell As Range, search_cell As Range
Dim i As Long
[Code]...
A few issues with the above code: I have manually checked that there is a sheet with a value in M12 that is found in the H column range, but it doesn't change the value to zero. (this was confirmed with an =IF(M12 = H101,1,0) returning a 1 value.
Secondly, it takes a long time to process not a lot of data. (about 2 minutes to get through 3 sheets of 180 actual rows of data, (rest blank). Is there any way I can speed it up? I will eventually be cranking it up to 30 sheets of data and up to 360 rows. (The 500 was thrown in earlier to make sure it covered everything).
finally, it also lines every cell value in I(end of data):I501with a zero, when there are blanks in other cells, can i get it to not put a zero in,
View 5 Replies
View Related
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
Jun 3, 2013
I have a list of 120 values in column A. Is there a quick way to search the entire workbook and return what tab the value appears in column B ?
View 2 Replies
View Related
