assuming we're comparing column a, i want to eliminate rows in which its column a value matches the column a value of a previous row.
what i have:
abcd
1351
2442
3235
4324
3542
2253
1344
what i want
abcd
1351
2442
3235
4324
i tried the advanced filter - copy unique record to another location but it would only copy the unique column a values to another location and not values on the same row attached to that column a value.
I have attached a spreadsheet of sample data. Column I has duplicates and the data in columns A to H are in any of the duplicates. I want to eliminate the duplicates and align the data in the other colums to the single row. Eg I2 and I3 are the same value, I want to end up with only one row with the value 27217 and the data in F2 and G3 to end up in the single row. If value 27217 is in I2 only then F2 will still contain 5 but 6 will be in G2 not G3.
I have 5 columns and 1000 rows of data, All cells has formulas, resulting blank or value, I need a formula to copy only the rows with values building another column (list) with no blank cells Is it possible without macro?
Duplication of zips appear in column A - and I only need to return the highest sales in B. So based on the example below..I only need to return $500 in column C.
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I am trying to solve a column total where rows that have a repeated column A value only have their column B value totaled once.
For example the table:
A B item1 20 item1 20 item2 50 item3 30
The total I am looking for column B is 100 (20+50+30) and not 120, as item1 should only be added once.
I made a few forum searches I made and did not find my answer, though I suspect this is a repeat question. I am guessing there may be a sumif(frequency solution out there that I'm just not hip to yet.
how do i copy one column to another one (with formula) that not repeat any number?
i have a list of prices and i need that list in other column with any price repeated.... and theses prices are imported data, so they change, thats way need to be formula =/
"A" is item number: "B" is sales Date: "C" is sales price.
What I would like to do is search column A and when there are two of the same item numbers know what date they sold and for what price. I would prefer to build another table that has only this data.
I have simplified this spreadsheet from the original to get to the core of what I want it to do. There are three tabs: 1) tab:"query fields" is an area that places data that is retrieved from the web, in this case it retrieves tables from yahoo finance given a ticker input. The ticker input cell is currently on the "score card" tab. 2) tab:"score card" in this tab you can input a ticker symbol in the indicated box and it will populate the "query fields" with corresponding data. This tab also extracts certain fields from the retrieved tables and performs a pass/fail test then produces a score %
This is great if I'm doing one symbol at a time, however I eventually would like to 'loop' this process such that a score field is populated for a list of tickers, hence tab 3) "list". Ideally I'd create a control button to initiate the web retrieval process and it would go down my list, retrieve the table data, perform the pass/fail tests, then populate the corresponding score % in the cell next to the ticker symbols. I know I can keep running separate web queries for specific symbols, but I guess my secondary goal is to have this be as dynamic as possible, since the ticker list will change and the list could be sizable at times.
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
Trying to manipulate basketball statistics copied from websites ... each cell has a leading space before the number(s) ... is there any way to globally eliminate the leading space from the cells
Tried find/replace, finding [space] replacing with 0 (which would then be disregarded ... didn't work
Tried converting all cells to number format, didn't work
I wonder if you can help me in this question. I have a MIN function which calculates the smallest value in a list of percentages and then divides each percentage by the smallest in the list. It works fine until one of the percentages becomes zero with the resulting division-by-zero error. Do you think it is possible to make the MIN function to exclude all instances (there may be more than one zero) of zeroes from its calculation? I have attached the spreadsheet.
I have recently been advised to eliminate goto statements. While I have not had any trouble with them so far, it is my understanding that a different structure will speed up my macro, which never hurts. Here I have printed a recent macro I wrote with two goto statements in it heart. Could someone show me how they would rewrite this bit of code while still getting the same results? Here the code compares the active cell value against B and if it does not match, it goes to the next value and compares. If it goes through more than 100 searchs and the term is not found, then it has checked the whole list and needs to stop search, thus the progression of C. A few options are offered before closing down the macro, but ultimtaly, C needs to get to 100 to exit the sub. Thus the goto Alpha that repeats the addition to C.
Alpha: If ActiveCell.Value CStr(B) Then ActiveCell.Offset(1, 0).Select If ActiveCell.Value = "" Then GoTo Beta C = C + 1 If C = 100 Then I = MsgBox("Complete. Do you want to continue with Received data?", vbYesNo) If I = vbYes Then Windows(D).Close Call ReceivedDataUpdate GoTo Epsilon End If
I have a 23 worksheet workbook that I am exporting to csv files. Somehow the data (formulas) appear to be included in the export, even though when I click on a cell in the csv file it does not show the formula. I do not want the data to be exported. Any suggestions on how to modify my macro so that only the values of the cells without the data get exported? Here's the macro:
Sub SaveAllSheets2CSV() Dim wsSheet As Worksheet With ActiveWorkbook For Each wsSheet In .Worksheets wsSheet.SaveAs Filename:=.Path & "" & wsSheet.Name, FileFormat:=xlCSV AddtoMRU = False Next wsSheet End With End Sub
I am having trouble with eliminating spaces from the value I've generated from my formula.
The formula will take the first 4 characters from a list of names, and add to that a number and a year. (For instance: "Conniff" would become "conn_01_09").
My problem is if I have "Ag Services". "ag s_01_09" would be generated. How can I eliminate that space? The list of names is in one field.
The formula I used so far is: =CONCATENATE(MID(E2,1,4),"_",MID(F2,1,2),"_",MID(C2,3,2))
I tried TRIM and CLEAN, but couldn't get them to work right.
Eliminating empty cell in the concatenate. I am concatenating many cell together. Example is Column A has a word " Starter" Column B is the type of tractor it fits " Case" Column C thru E have other names IE John Deere, Ford only if the part fits more than one type of tractor. In my final Concatenate i end up with many comma's and nothing follows. I have placed a ", " in between each reference of the cell. If the cell is 0 or null. I want the concatenate to ignore the cell and extra commas.
My current function reads + Concatenate(A1,", For ",B2,", ",C2,", ",D2,", ",E2)
What I have now. Starter, For John Deere, , , , , , , . Starter, For John Deere, Ford, , , , , , .
I have a formula that is dependent upon a column of cells containing text. Cells within this column randomly have an additional space (" ") following the words. With this invisible space, the formula doesn't work as intended.
Since I have an extremely long list of names, is there a way to easily remove additional spaces after words without manually going through each cell and deleting them?
I want to do is create a template that can be used for generating an attendance sheet with corresponding statistics on a semester by semester basis.
I've attached the workbook im using. For the purposes of my query only columns B-F are relevant. What i want is a formula that can compile all the names and information in columns b-f on a seperate sheet (sheet1)
I want to make sure that duplicates are eliminated from my final list so that even if a name appears multiple times in the sheets titled sessions 1-5 it only appears once in sheet1.
I want to do this in a formula so that it can be used over and over again. If possible I would also like the formula to exclude blank rows in columns b-f in the final list.
Coworker was sent a spreadsheet with a singe column of loan numbers; some start with zeros, some don't. But every single cell ends with a "^". (Shift key and 6)
What is the easiest method to eliminate non-corresponding items from appearing in pivot fileds. For example, if I select "Texas" and "Arizona" from my state pivotfield, the "City" pivotfield next to it should only display cities within these states, and not every city in every state within the original data.
I presume this involves dynamic ranges and also some sort of array matching?
I've been asked to streamline some processes for a company I used to work with before I went on maternity leave (been two years now, my brain feels like its gone to mush). One of the things they have is a document where they input EVERYTHING manually into a word, sometimes the same thing several times. (felt like I had gone back into the dark ages). I know I can set up a template in Excel where all the required information can be imported into specific places within the word document, but Im struggling to find a solution to one part.
They get their clients to fill out an expenses form showing all their monthly expenses, some fields will be filled in but others are not relevant to them so they are left as zero.
How do I go about just importing ONLY the fields that have values input against them?
For example;
Mortgage $2000 Rent $0 Electricity $300 School Fees $0 Contents Insurance $200 Water $80 Pet Care $50 Groceries $1000 Takeaways $0
Then when the data is imported into word it would show up like this (formatted better of course)
Mortgage $2000 Electricity $300 Contents Insurance $200 Water $80 Pet Care $50 Groceries $1000
