i need to lookup in a sheet for cells that start for example by "SE=", and copy them to another Sheet creating a list!
But some of this cells started by "SE =" are repeated, like:
SE = cars
SE = cars
SE = cars
SE = dogs
SE = dogs
SE = bike's
and this is what I'll need:
SE = cars
SE = dogs
SE = bike's
I have a column that contains labels that are entered down to row 400. Below row 400 in the same column are formulas. If I do a filter on that column I get formula results in the filter list from the formula cells. Is there a way to omit the formula cells on the column that's being filtered? In other words, only list data in rows down to 400 in the filter list.
View 2 Replies View RelatedI 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.
unique list.xlsx
I am working on an email marketing project and i have a small problem. I have two different email list. One (List A) is a large list of potential leads. The other (List B) is a list of leads we are not supposed to market to. I need to delete every lead on List A whose email address is also in List B, so that we do not send unwanted emails to our clients.
The best way i have to do this so far is to go through line by line, which is very impractical.
In case it matters here is out list format. Each list has 10,000 + leads. Each lead occupies a row. The row stretches across 13 columns and each column holds a different variable about the lead (names, state, email address).
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
I have a list of data which looks like this
Column B
Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
I do not have any code for this as I am unsure if it is possible. I have a list of range names in a column (A) that refer to different sheets and in an adjacent column (B) I have a list of data. Is it possible to write VBA code that will allow me to copy each value in column B to the corresponding cell in the workbook that relates to the range name in column A.
View 2 Replies View RelatedThis is what I have and it go down to 200 names.
Andrew
Andrew
Andrew
Constance
Constance
Constance
Curtis
[Code]...
This is what I am looking for a formulas that will do this take out the dup's (Not Conditional Formatting)
Andrew
Constance
Curtis
Greg
Pauline
[Code]....
I need to check a list of names to see if there are duplicate entries.
At the bottom of the list I would like to count the number of duplicates.
In the list I want conditional formatting to highlight cells where the duplicate appears.
Using Excel 2007.
I am using a simple =Right(A1,4) is there a way to change this so it looks in cell A1 and if the is character is a zero then only give me the 3 characters fromthe right?
so if A1 = sweden 2041 it would give me 2041
but if A1 = sweden 0411 it would give me 411.
Here is the deal I have 4 columns. Each line gives you the following information:
The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.
ID
CODE_NAME
Date
Area
Logical
116
SSS1
02-Jan-09
1A1
273
RRR1
02-Jan-09
2A2
2959
BBB1
02-Jan-09
1B1
2959
What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not.
So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.
The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.
I need to identify duplicates in a list and have the foilowing formula:
=IF( COUNTIF(range1,A2)>1,"Duplicate","")
This works ok but i have a further condition which i dont know how to factor into the formula. I think i could write some vba to determine the dupes but i was hoping to avoid this as im sure it will take me an hour or so. Duplicates are identified at the moment as being identical numbers in column "amount", i now need to specify duplicates as being identical numbers in this range where there is at least one row with no pay date filled in in col "paydate"
I was wondering if there is a formula or a vba code to return each number once from a list.
For example I’ve got a list with app. 500 rows and maybe 80 different numbers in there, so one number could be 50 times in the list, another number maybe just once. Is there a formula, which returns me each number once?
Can’t think of any, or do I have to use a vba code be get the information?
I am using Excel 2003.
I have pulled various data points from a pivot table into a summary.
My pivot table is located on a tab entitled "Pivots - All".
My summary tab is entitled "Open Summary".
My chart tab is entitled "Open Charts".
The formula within the summary cell that is pulling from the pivot equals
=GETPIVOTDATA("Active Y/N",'Pivots - All'!$A$70,"Active Y/N","Yes","Leader","Eurich","Expected Tenure",4)
I have found ways to NOT display the zero values in the summary tab, but the column chart (which is set to use value as the data labels, continues to pull the zeroes in the chart.
Is there a way around this besides using the "clear all" within the summary? I refresh this data weekly and don't want to have to keep redirecting the cells in the summary to the pivot.
given code on this forum which created a "Search Box" on my spreadsheet.
The one thing that I would like to tweak however if that it finds the word/number that has been inputted in the search box itself (as well as finding the other genuine entries). Is there a way that I can search the whole sheet apart from cell C2 (The search box cell)?
Here is the existing
Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range
Dim more As Boolean
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Column = 4 And Target.Row 4 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
End If
I have four cells that I want to sum: =SUM(D3,H3,L3,P3)
I want to EXCLUDE the cell from the sum if the preceeding cell (C3,I3,K3,O3) has a value of "0".
I'm working on a report for work in which I need to produce sevearl charts. For each of these charts I need to omit any values that are 0 or null. Since this report is going to be run several times a month and with different values it would be pointless for me to do it by hand.
View 9 Replies View RelatedIn A3 is a surname, in B3 is a first name (and possibly multiple middle names, separated by a space). In C3 I'd like the first name ONLY and the surname
A3......................B3.................C3
Hobbs.................Jon Peter........Jon Hobbs
Peters.................Mark..............Mark Peters
Jones..................Bob Tim Mark...Bob Jones
In some cells the format (all in the same cell) is:
A3
Broadly, my workbook contains 5 worksheets. Worksheets 1, 2 and 3 contain calculations, worksheet 4 contains a summary of calculations from worksheets 1, 2 and 3. Worksheet 5 is a data table used for worksheets 1, 2 and 3.
I need the worksheets to calculate in the following order:
1. Worksheets 1 and 2 extract data from worksheet 5.
2. Worksheet 4 captures that data.
3. Worksheet 3 uses the data from worksheet 4 and extracts data from worksheet 5.
4. worksheets 1 and 2 recalculate using the calculation from worksheet 3.
5. Worksheet 4 captures the refreshed data from worksheets 1 and 2.
Is there a way, using formulas in the worksheets, to ignore worksheet 3 on the first iteration of worksheets 1 and 2, and prevent recalculation on the second iteration of worksheet 1 and 2?
In my mind I can see Excel simply recalculating over and over again, or will it stop. Is there actually a "problem" here or am I perceiving something that will not occur?
I have data in the form of a table. For example a list of duplicate names, each of these names have a corresponding date (lease expiry date). The problem is that the duplicate names have varying dates. So the remove duplicates function does not work because i need to remove the duplicate names with the older dates. I want the latest dates to remain behind.
Data Currently: Solution should be:
Sue1/3/2014Mike8/8/2014
Jay25/4/2013Sue1/3/2014
Jay25/4/2013Jay25/4/2013
Mike8/8/2014
Mike8/8/2014
Sue1/3/2014
Sue25/6/2012
Sue1/3/2014
Sue5/7/2012
Jay2/2/2011
Mike5/5/2010
I have a list of what we call "model codes" which are is a similar format to "DFS41FC57DD728NCWRY3"
The list could be 6000 rows but may contain hundreds of duplicates and may only contain 50 different model codes
I am looking for a formula that will populate a new column (B in the example below) with only the individual codes (unfortunately the xls example I created wont upload)
COLUMN A COLUMN B
DFS41FC57DD728N3SRY3 DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3 DFS41FC57DD728NCWRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728NCWRY3
I have a list of objects:
A
B
C
D
X
D
A
F
G
H
I know how to get a unique list. How do I identify those that are in the list more than once and how many times it is in the list?
I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)
I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.
I need to use this information in a Validation List on my "Inventory" sheet in C1
The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.
I have a list of Dates in Col. A
Column B contains both numerical and text values.
I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.
A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8
Value needed = Vac
Solution 1/4
1/7
I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?
I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).
(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)
I am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.
RED
GREEN
YELLOW
PURPLE
RED
ORANGE
BLACK
GREEN
RED
Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output
RED 1
GREEN 1
YELLOW
PURPLE
RED 2
ORANGE
BLACK
GREEN 2
RED 3
Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.
I am making an excel document that has a log of repeating data, one column of which includes names. At the bottom of the document, I am trying to make a small box that tallies all of the people within the document and the number of times they placed an order. What is the formula for when you want excel to find a word within the document? Right now I am using =CELL("CONTENTS",C6) etc. So all that does is repeat the word when it is used. But it duplicates the word in the box down below because it appears in the log numerous times.
View 7 Replies View RelatedHow to calculate the average of a set of numbers through excel formula when I want to ignore few skewed values. e.g. 1,3,2,5,90,2,4,56. I want to calculate average of the above set by ignoring the effect of two skewed numbers viz. 90 and 56.
View 3 Replies View RelatedI have a formula to calculate an average value for a reference range:
= SUMIF(DATA!B32:B61,"<># div/0")/MAX(1,COUNTIF(DATA!B32:B61,))
Because these cells are references the blanks are being treated as zeros so I am getting an inaccurate average value.
Is there a way to make it so it will treat blanks as blanks? I need the zeros to be zeros.
In a database of names I use Filter- Advanced Filter - Unique records, to hide duplicated rows. Trouble is I don't know if there were any duplicated rows when it finishes. I would like to see the totals reflect this by not including them in the Countif function.
View 6 Replies View Related