Find High And Low Values Of Column According To Values Of Another Column?
Jul 23, 2014Ccheck the attached sheet for the explanation.
Book1.xlsx
ADVERTISEMENT
Find / Replace From Column A Values To Column B Values VBA
Mar 6, 2014I have a workbook with 20 sheets in English Language, but I must to translate in Russian. Translating has done by human translator, so translation is good. I must change phrases and words in english to russian. I can use find/replace function but doing by hand it's very tie consuming. I need VBA or macro who takes values in column A, finds it in entire worksheet and changes it to matching value in column B. Is it possible and how I can do it?
View 1 Replies View RelatedFind Lowest 5 Numbers In Column A With Highest Values In Column B
Oct 5, 2013I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.
1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00
Find Two Values In One Column?
Mar 11, 2014I've got two question on how to 'find' some values in a specific data file, namely:
- the '52-Week High' of the share price (highest value of the share in that specific year)
- the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)
Simplified, my data looks as follows:
-In column 'A' I've a created a line of unique edentifiers (company CUSIP + year -> combined column D and E), in my set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the date is shown -> its daily trading data
-In column 'C' the daily share price is shown
-In column 'D' the company's CUSIP (edentifier for specific company)
-In column 'E' the year
Sheet 1:
1.JPG
My final data set has to look something like this:
-In column 'A' the unique edentifiers (company CUSIP + year)
-In column 'B' the specific year
-In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1
-In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1
-> off course all the empty cells in column C and D normally contain the missing values
Sheet 2:
2.JPG
The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?
Regarding to the '52-Week High' (red colored in the sheets) I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' this would become '12'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 1: this would be '115'
Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.
Find X Values In Column
Aug 3, 2007I'm trying to do is scan down Column A (1:100 would be more than enough) and looking for 3 values, "INCOMING:", "OUTGOING:" and "WEBALERT". These will always be under Column A but the lines of text between them are different everytime. I'd like to capture the Row values of each of these values and store it in a variable which I will use in a range later. I think the way is to use a loop, but for the life of me I can't figure out how to get it to scan.
View 7 Replies View RelatedVBA - Generate Random Column Values From Existing Column Values?
Aug 22, 2013[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
Extract Largest Values From Column B And Corresponding Text Values In Column A?
Oct 1, 2013There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
Calculate Mean Of Column B Values Based On Date Values In Column A?
Dec 5, 2013Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?
Dec 24, 2013I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
[URL]
Counting Values Of One Column Based On The Uniqueness Of Values In Another Column
Jun 11, 2014Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
Find All Values Under Column And Paste
Aug 18, 2008whats the most effiecient way to find all values under column B2 and all values in column O2 and paste them in column U2?
View 9 Replies View RelatedFind Unique Values Under A Column
Jan 22, 2009In an excel sheet i have around 10 columns.
I filtered the data based on the column1 using vba. Then after that, the filtered data in column2 have repeat finance accounts. What i need to find the unique finance accounts under the column2.
Is there any methods to find the unique value under the column2?
Find Specified Text In Column And Sum Values Beneath It?
Jun 4, 2013I have some xls files. I need to find the sum of a specific column. the column heading is always TOTAL but the column it resides in changes column to column. so one file may have the text total in column H and others will have the text total in column X. how can i make a macro so it finds the text 'total' and then sums up all the values in that column underneath it and displays it at the very bottom of that column?
View 9 Replies View RelatedColumn SUM Produces #N/A (Vlookup To Find Values That I Want To SUM)
Dec 24, 2008I have a column using Vlookup to find values that I want to SUM.
Some of the look up values produce a #N/A and result in a total sum of #N/A.
How do I get the SUM of a column of numbers when all of the cell values are not in fact numbers.
Find Records And Return Values To New Column
Nov 20, 2009I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
Find Values From Column In Another & Copy All Occurrences
Aug 16, 2007I have created (pieced) together a macro to search through a list and paste the results in another worksheet it works fine but i can't get it to loop through the list.
Sub Findall()
Dim y As Long
Dim starta As String
Dim tr As Long, tc As Long
Dim sr As Long, sc As Long
Dim s As Worksheet, t As Worksheet
Dim SourceCell As Range
'Setup
Application. ScreenUpdating = False
Set t = Sheets("Target Sheet")
Set s = Sheets("Search sheet")
Set SourceCell = ActiveCell
Do While IsEmpty(ActiveCell) = False
Counter = 1
'Get last used row in Target Sheet
t.Select..........................
Find All Cell Values In Column And Delete Row
Jan 25, 2008My code is always failing on the findnext method. I'm thinking that the findnext method fails because it loses it's cell reference when the row gets deleted.
Sub findwbs()
Dim rngFind As Range
Dim strFirstAddress As String
Find Values In 2 Columns And Return A Value In A 3rd Column
Jan 30, 2008I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies View RelatedFind The High Price Day Of Each Week. Then Report The Median High Day
Aug 14, 2008I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....
View 9 Replies View RelatedLookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
Jan 31, 2008I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
Find Most Occurring Values In Column A And Consolidating Each Value Quantity?
Jun 12, 2014As part of my work as an intern, my company wants me to look at all of the office supplies ordered by their retail stores and find out which items are most ordered.
The invoice excel file I received from our office supplier has a column for the Item # and Quantity of that item. However, there are multiple occurrences of each Item # because this invoice is itemized by store. Is there a way to consolidate all of the values in the Item # with each other to remove the duplicates while also consolidating all of the quantities of each?
For example:
Office Supplies 2.PNG
The invoice is much longer and has many more different Item #s and many more duplicates but this is more or less the same format.
I used COUNTIF with an advanced filter with Unique Records Only selected but I realized that only gave me the count of the Item #s and didnt factor in the Quantity of each Item # ordered by each retail location.
Find Column Values Between 2 Files & Copy Paste Into Another
Jul 31, 20081. For a file moving from one cell to the next, down the column, get the values and seach for the values in file number two.
2. If that value is found, copy a cell from file 1 to a cell of a column on the same row where the value was found on.
3. Do what was done on number two, but with a different column.
this is what i have so far...
Sub replace()
'
' Macro2 created by david
'
' for the entire sheet, moving from one cell to the next down the column, changing rows
' search for the contents in another sheet, and then if that is found,
' copy the row number to a variable, and then
' copy column K from sheet 1 to column N of sheet 2, using the same variable above
' after that same, but L goes to P.
'
'
Windows("file1.xls").Activate
Rng = Selection.Rows.Count
Dim toFind As String
ActiveCell.Offset(0, 0).Select
Application. ScreenUpdating = False
Dim i As Integer
For i = 1 To Rng
Windows("file1.xls").Activate
toFind = Range("A + i")
Windows("file2.xls").Activate
Summing Values Of One Column Based On Values Of Another Column?
Feb 21, 2013I am trying to sum certain values in one column based on the values of another column. I made a few tables, generated some random numbers, and then I started writing up an if-then statement to see if I could get what I am trying to do to work. However, I was having difficulty getting the program to run, because I was declaring more than one variable (I think).
So for instance, I want cells in column G to sum forces in column C when a joint in column E matches a joint in column A. Similarly, I am trying to do the same thing for columns F and B.
I have attached a picture, which shows what I would like for the program to do automatically (rather than me doing it all manually)! I didn't include the code I wrote, because it was so minimal
Attachment 51660
Filtering Values In One Column Based On Values In Another Column
Apr 7, 2014I'm currently working on a spreadsheet that contains 30,000 leads and their respective companies and i need to filter out any leads that work for companies present in a different list of companies (1,834 companies). Basically, i need to filter column A so that it only contains companies present in column B. Is there a way to do this using the advanced filter?
View 1 Replies View RelatedMatch Values In One Column To Values In Another With A 3rd Column Included
Nov 26, 2009I have about 360 records in Column 1 and less columns in Column 2 (333 in this case). All the values in Column 2 are in Column 1. I need to be able to match those two columns up while keeping the data in Column 3 intact with it's corresponding value in Column 2. FYI I searched and found this thread: ttp://www.excelforum.com/excel-work...n-another.html
But my problem is that I have to tie in the values with column 3 to their corresponding values in column 2. In order to illustrate, I have attached an excel file in which I did the operation manually. (note: all values in column 2 are indeed in column 1 but not in the example as I have shown for the sake of convenience).
Sort Values In Column B To Match Values In Column A?
Jul 8, 2013I have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:
2012
2013
270806
330011
330011
350529
350529
369916
to:
2012
2013
270806
0
330011
330011
350529
350529
0
369916
So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.
Summing Values In Column Based On Values In Other Column
Nov 18, 2009I have a vba form with three comboboxes. The first combobox contains a list of employees, the 2nd a list of work activities and the third a list containing hours. As each employee enters the data for their name, activity and hours these values are saved to sheet6 starting at A10, B10 and C10. I want to be able to sum the hours for each employee who enters data. So the completed user form could look like the following:
HTML Employee (A) Activity (B) Hours (C)
Joe Swept 1.25
Raked 1
Bill Mowed 2
Cleaned 1
Joe Trimmed 1
So as you can see there could be a blank cell in column
"A" under Joe and Bill and Joe could come back later and
enter more info. What I want on sheet6 say starting at A30 the value
Joe would be entered and in B30 his total hours 3.25. In A31
Bill would be entered and in B31 his total hours 3 and so on for other
employees
Copy Values From Column A Based Upon Values In Column B
Jan 2, 2010If values in column B are alike or if only one value exist in column B, I would like to copy values from column A over into columns D and all other columns to the right of D,
Before and after examples are below....
Excel 2010 :: How To Find All Unique Values Then Combine In A New Column
Jul 9, 2013I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
Serial Numbers | Barcodes | Combined Values
-------------------------------------------------------
abc123 abc12300 abc12300,abc12301, etc etc
abc123 abc12301
def456 def45600 def45600, def45601, etc etc
ghi789 def45600
i am using Excel 2010.
VBA / Find Column Header And Reformat (date) Values In Columns
Jun 28, 2013I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....