I would like to create a macro which will look at columns A,B and C. Based on A,B and C, I would like the macro to return only the unique combinations. At the same time, I would like columns D and E to perform a sum.
This is the original sheet:
******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE1015D4MAYBE101NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is what the sheet will look like once the macro I would like to create is executed.
******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE202NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
As you can see, the highlighted areas denotes the changes that were executed through the macro.
I have 36 cells that will have multiple letter values. The cells in the range (MyRange) in question, have formulas in them. I will be transferring this into a cell range that has over 6000 cells, but I'll worry about that when I get this part down. :D I have 07 excel but this is in the older versions.
I am looking to do find the latest date in a list of dates but have several problems: 1) The dates are all in one column on worksheet 1, but are sourced from other worksheets. So even though it looks like '8/7/09', the value of the cell is 'Worksheet2!B4'. This is making the MAX function not work
2) How to deal with the blank cells in the MAX function?
The thing is that i have alot of sheets (more than 20) and my program picks 2 values from the same cell locations on each sheet. This leeds to more than 30 arguments in my formula. I am gonna use this for alot of excelfiles constructed in the same way, but with varying order and names on the sheets.
I have successfully loaded a combo box lookup in one cell referencing a column in another worksheet. While the drop down is pulling in all the names listed, there are duplicates in the data set. I want the drop down to only pull unique names. Is there a way I can do this and still use the combo box feature.
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
I have a column with unique identifiers (numeric) that are included in another column. I need to find this number in the column and pull the data from that column into a new one. For example:
Unique Identifier: 28530
Is included in this string: 10.1.8........28530_201_10-....
I need the content from the string pulled into a new column. It seems there would be a LOOKUP that would work, but I'm not able to figure it out.
I need to place a value in cell B on sheet 2 of a workbook. The value I need is stored in C10 on all other worksheets in the same workbook. The name of each worksheet is stored in Column A of sheet 2 (Rows 3 through 404). How do I get the value from C10 on each worksheet into the corresponding row on Sheet 2?
I have Excel for Auditors and there is code on page 201 but it is the reverse of what I need. I need to get the value from the worksheets as opposed to moving a value to the corresponding worksheet.
Example:
Sheet 2 Column A Row 1 = Worksheet name Column B Row 1 = Need Cell C10 value from worksheet name referenced in A
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.
A B 1 oakland.california.usa california
Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output A | B Z 1| 10 | a 10,a,b,c 2| 10 | b 11,a 3| 10 | c 12,a,b 4| 11 | a 5| 12 | a 6| 12 | b
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B AA | AA DD | CC AA | DD CC | DD | DD | IC | IC |
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is {=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1, IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1)) + SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1, IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3 All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I don't really know how to search this question but what I am trying to do is pull information from one worksheet to another only containing numbers greater than 0. For example, i have an order sheet containing everything that is in stock, then I go down the list and put in the number of items being sold (1,2,3, etc). Some items will have a value of zero.
Then the items that have numbers (the ones that are being sold) I want to be able to pull these numbers along with the item description onto another worksheet which will be the invoice. Then excel will add up the prices of only the items being sold and give me a total value on the invoice. Is this doable?
I have this problem I have 4 columns which contain data. The first column is a list which contains random values from the third column. Now what i want to do is to pull the duplicate values from the second list without agitating the data from the 4th column because it is corresponding.In the example i have attached i would like to take the values from column A and find them in column F then extract them together with corresponding value from column G
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
I have two spreadsheets, i suppose one part of the solution may be to copy spreadsheet 2 into a new worksheet in spreadsheet 1, but this is my current situation:
Spreadsheet 1 has "Employee lastname" in column F, "Employee Firstname" in column G. Column A in this sheet needs updating with the data populated in column H for that employee in spreadsheet 2 - the lastname is contained in column B, firstname in column C - so i need to to a match.
I am trying to pull peoples names from 3 diferent tabs into one column in a separate tab. But if a name is shown more than once only display it once. How can I do this?
I am trying to replicate a payslip from a list of data on a worksheet.
The list of data contains the employee name, location they worked, and number of hours.
Each employee will work at multiple locations throughout the month, perhaps 10 or so.
The payslip must contain each location worked along with the relevant data, in a list so to speak.
What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?
I want to pull 'values' from a website off into column's "A" "B" and "C."
What I am looking for is:
Column A: The current date! Column B: The closing VIX value of the day! Column C: The 10-day-moving average value of the VIX for that same day!
All values come from this website: [URL].......
(full link so it doesn't look sketchy; also a link to mainwebsite: [URL] ..... )
What would be the formula for this? Or how would one go about this? And preferably the time to pull these values would be at alterable, but ideal time is hard to choose right now... Need to think about it!!!
I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes.
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D See attachment.
I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?
I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.
