I have a column that contains different text values. In another column I have numerical values. I'm trying to create a formula that will first look in the text column for a certain name, if the name matches then look in the number column for a specific number and then count the number of cells with that specific number.
One of the challenges is that the number of names will vary and the names aren't in any type of order.
There 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.
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
I have text values in A column which i want to break in columns B, C and D. It looks like example bellow
90.90.7
90
90
7
110.110.12
110
110
12
60.90.6
60
90
6
100.100.8
100
100
8
I have hundreds of rows to deal with.
I 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 RelatedI 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]
I'm trying to execute something like this: [URL] ......
but without changing the cell value... I basically want a button that will wrap/unwrap all of the cells in the "B" column with one click. If i have to do the cell range for every individual cell in column B that's fine, but I can't seem to make the above example work while keeping the cell contents the same.
What can I add to this existing formula to look for duplicate values in the entire column (D) and then concatenate the text of column (K) in each of those rows together?
Essentially I want to string all text in column K together, for each duplicate row of column D with the results in column L.
=IF(D14=D15,CONCATENATE(K14,K15),K14)
I am wondering if it is possible to format a cell that contains text - based on a number in the cell next to it. Here is what I have:
Category......Mean
Results.........3.3
Morale..........3.8
Buy-In..........4.0
I would like the word 'Results' to display in BLACK text because the mean score is in the range 3.0-3.9 (same goes for the word 'Morale'). I would like the word 'Buy-In' to disply in GREEN text because the mean score is in the range 4.0-5.0. Basically, the mean scores are calculated using formulas and I would like the color of the category to change based on the mean score number. Is there a way to use conditional formatting to do this? Or another way to automatically make this work? File is attached.
I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
excel.jpg
I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.
So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.
First column
Second column
Date
Same
Good
Bad
[Code]....
I have attached the file, Problem.xlsx
How I will populate data of Weekday through the cells.
Is there a formula or function to populate data.
I am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.
Everything I keep trying ends up highlighting the entire workbook.
I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.
I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.
1. The Text to column function and separate values by the ";" sign
2. Then separate the first column ( which contains both date and time ) into their separate
columns.
3. The 2nd step will result in values such as "mm/dd/yyyy 0:00" and "hh:mm:00" followed by
an "AM" or "PM" which is unnecessary. The code should clean up the resulting date and
time into "mm/dd/yyyy" and "hh:mm" respectively.
4. Flip the rows upside down so that time is decreasing downwards.
The resulting spreadsheet should contain 6 columns and should look like:
12/5/200810:421.266491.26651.266091.26629
12/5/200810:431.266291.266431.266041.26609
12/5/200810:441.266091.266131.265641.26593
12/5/200810:451.265931.266681.265741.26604
12/5/200810:461.266041.26671.266011.26645
[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.
Below 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
I 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]
Here 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.
I'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.
I 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
Ccheck the attached sheet for the explanation.
Book1.xlsx
I'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 RelatedI 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).
I 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.
I 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 RelatedI 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
If 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....