In the attached file column A has ID's. In the attached example i have used 2 ID's 141020061 & 151020062. I need to find the last entry of each ID and sum the value from the column F. that is the last entry for the ID 141020061 is 40500 and for 151020062 is 0 so the total should be 40500.
View 9 RepliesI have a sheet "Pattern" with a series of statements in column A, starting at row 12. These contain a lot of common words like 'a', 'and', 'that' etc which I would like to automatically remove. On a separate sheet 'Library' in column D I have a list of these common words which I would like to look through one by one and remove from the statements on the 'Pattern' sheet.
The code I have at the moment looks like,
Sub CommonWords()
For i = 3 To 65536
Dim w3 As String
w3 = Sheets("Library").Cells(i, 4).Value
Sheets("Pattern").Range("A12:A65536").Select
Selection.Replace What:=w3, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub
But this only achieves to remove the contents of the 'Pattern' sheet, column A..?? Where am I going wrong? Also, with this method if I find and replace 'a' with the 'LookAt:=xlPart' option, is this going to remove all 'a' 's, even if they are in the middle of a word?
I have three columns
"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.
In colmn A:A i need returned, which numbers (if any) are repeated and how often each repeated number occured.
Admitedly... I'm lost on this one.
I have two sheets, where I am trying to look up the value from one sheet and post it to the other.
On sheet 1 I have the Names of the Individuals, On sheet 2 I have the Names of the individuals in a different order and then in the next column i have the amount of dollars they spent on a product.
The reason I don't think I can do a simple vlookup here is that sometimes the names are listed Multiple times each because they made seperate purchases.
So for example
Sheet 1 SHeet 2
John Doe Jane Doe $1,000
John Doe Michael Jordan $2,000
Jane Doe John Doe $3,000
Michael Jordan Michael Jackson $4,000
Michael Jackson John Doe $5,000
If I did a vlookup, I assume that John Doe would return $3,000 for both. I would like to return $3,000 for one and $5,000 for another.
Is there a quick command that I can use to take
math
math
math
math
art
art
art
art
art
music
music
music
music
into
math
art
music
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.
I have some textboxes named value1 through to value10, and have got consistent, sequential coding in the change event of each box to perform some actions (enable some other textboxes.)
I was wondering if there was a way to tidy my coding so that I do not have 10 change events that are essentiallly the same except for their sequential numbering. This may be impossible, as I think I have to have a macro for each change event, but thought I ask just in case there is a way.
Private Sub value1_change()
If value1 "" Then
markup1.Enabled = True
Quote1.Enabled = True
Else
markup1.Value = ""
Quote1.Value = ""
markup1.Enabled = False
Quote1.Enabled = False
End If.......................
Would like to have a formula for removing duplicate row values from the attached sheet.
In simple, in the first row, Name1 and Name2 are repeating. And the requirement is to remove the repeated value "A" from Name2.
I have 1 to 30 spread sheet plus a Sheet with TOTAL , each represents one day ,
on each sheet I have one column and 6 lines where I will please random numbers from 1 - 50 but six number on each sheet
The question is on the total sheet I would like to have automatically the number repeats the most on those 31 sheets .
i have one Q??
i have these times
07:00
07:43
04:55
07:43
is it possible to COUNTIF the repeated hours? the result should be 3 "07:??"
how to delete repeated rows and just keep the first one found?
View 13 Replies View RelatedI have a table with some reapeated words in, I wondered if anyone had any methods of counting words and displaying the total in number format. for example if I had in cell A1-Hello and A2-Hello and A3-Bye how can I get Hello = 2 and Bye =1
View 9 Replies View RelatedI have a list of products more than 5000 positions (rows)
Some positions are repeated (It could be 1000 rows with "Pen" position)
Based on this I need to create new short list which will contain the list of ONLY UNIQUE positions.
The same function is: DATA - FILTER - ADVANCED FILTER - UNIQUE RECORD ONLY
It is only filter, but i need the same functionality for new short unique list
I have 1-d array that gets populated from a number of sheets.
This array may contain duplicate entries... for example,
let's say this array contains animals.. cows, chickens, horses, pigs... etc.
What I would like to do is take this info from this array and create another where it would contain all the different types of animals but without all the repeats.
I have the regular headers in the first row, followed by rows containing my data. And at x number of rows, a new row containing headers appear again, followed by rows containing data. This goes on for the rest of report. What I need to do is remove the rest of the headers and lines because I only need to have one header in the first row.
Note that the header takes up two rows (one for the actual headers and one for the ====== lines. So removing the repeated headers is removing 2 rows.
For eg, part of the report looks like this:
PRODUCT PRODUCT NAME ==============================================12342EXAMPLE PRODUCT 122525EXAMPLE PRODUCT 2 35476EXAMPLE PRODUCT 3 53467EXAMPLE PRODUCT 4
PRODUCT PRODUCT NAME ==============================================135892EXAMPLE PRODUCT 5
I have a spreadsheet (>10,000 rows) with data referring to a grid cell map. Each grid cell on the map has an ID and associated data. About 10% of the grid cells have duplicate records, but with different data. I need to produce a spreadsheet with a single record for each grid cell that contains the data from each of the duplicate grid cells. Some occurrences may be >2. Example below.
Current
[TABLE]Grid#;Data
1;a
1;c
2;b
3;a
4;b
4;c
5;d
5;d
5;f
[/TABLE]
Needed
[TABLE]
Grid#;Data1;Data2;Data3
1;a;c
2;b
3;a
4;b;c
5;d;d;f
[/TABLE]
What I’ve done is mix up set of results to give me every combination of 8 sets of 3 (6500 combinations), when I summed every row (combination), and sorted then numerically it looks like I have some combinations that are exactly the same.
View 3 Replies View RelatedI am trying to write some code that will look in an excel cell find if it contains the word that I am looking for and make it bold. I have written the following code so far
VB:
With Worksheets("Label Print").Cells(i, J)
.Characters(Start:=InStr(.Value, "Name"), Length:=Len("Name")).Font.Bold = True
End With
[COLOR=#3E3E3E][B][/B][/COLOR]
The issue is that if "Name" occurs twice (or more) in a cell it will highlight only the first.
I have a number and i wanted to convert it to a number or character.
Ex.
if i have value of 7, should be converted to a character of "*", the output will be 7 times of "*" (*******)
if value is 10, so it will be 10 times "*". (**********)
Please find the attached file : <removed repeated characteristics with 1 criteria.xlsx>
I want to remove the repeated characteristics and it is to be plotted once also `XXX' should be removed and should not present anywhere.
I create a monthly excel sheet regarding customer & their payment
like this..
Suppose..
Month January 2007
Customer A 2000
Customer B 2000
Cust C 3000
Feb
Cust D 3000
Cust E 4000
Cust A 2000
Mar
Cust B 2000
Cust A 2000
Cust G 4000
Now, I want to do somthing like that If a customers Name repeat in another month, I mean Cust. A name has been already entered in Month January & if it comes again in february then it should be automatically replaced or highlight with red line Or something like that.
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 =/
I want to count the nr of words in cell without counting the repeated words.
For example, in "this is a ball and that is a square", the total nr of words is 9, and the total nr of non-repeated words is 7, because "is" and "a" are repeated twice, and I just want to count it once.
I have excel data containing in column A "Plot number" which is repeated and other four column is land area and the next column is "Land Owner Name". Now I want to merge same plot no if the land area is same if differ then I want to leave as it is. And another thing I want to do that the all land owner name is merge in a row.
View 8 Replies View RelatedI've data in a sheet (case1) which I would like to paste in a new sheet (case2) in the shown format. Is there any easy way of automating this? I tried recording a macro & tried somehow to automate this task but gave up after a few tries?
View 8 Replies View RelatedI'm just using the "=COUNTIF" function to count how many times a particular website was repeated, but I have no idea if such website is among the top five that appear the most throughout. Finding that manually, given the ridiculous size of the data provided, would take days!
View 4 Replies View RelatedI have a macro which allows a user to paste a list of equipment ID names into a range. The macro will then make a copy of a template worksheet for each equipment ID and add it to the workbook. I find that when I run this for more than 20 or so equipment IDs the macro will fail without an error. Even when I stop the macro I cannot manually copy worksheets. It seems to be a memory issue. If I save and close the sheet and re-open I can copy and paste manually again.
The particular section of code where the macro hangs is the third line of the for loop below.
Code:
For Each c In Selection.Cells
tName = c.Value
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets("Definition")
ActiveSheet.Name = tName
...more operations...
Next c
how to prevent this happening or more details on what is going on?
I have a table like this. I tried using a formula to get the return code values ​​equal no more have exito.
45
2
43
5
45
89
76
[code].....
How to do VLOOKUP bring the values ​​of the numbers "1".?
Is that possible to return the repeated elements from the range for example
Elements
Repeated Elements
A
A
A
B
[code]....