I have one columA with around 30000 item numbers and column b with dates .
Colum A has duplicate items which has different dates associated with them.
i want to remove duplicates in column A but the problem is i want the unique values with the most recent date.
I receive a stock on hand report daily which containers 7,000 + lines each day.
I want, in another sheet to show me what new product codes have appeared when compared against the previous day, for example
Date Product #Qty
1/10/201312341
1/10/201312351
1/10/201312361
1/10/201312371
2/10/201312341
2/10/201312351
2/10/201312361
2/10/201312371
2/10/201312381
So in a new sheet I want to show Product # "1238" as this did not appear on the previous days list
I want to count the unique values between 2 dates. He only needs to count the values that match 2 criteria. I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.
In my Data sheet:
Colomn X: Customer name
Colomn D: Status
Colomn AH: Date of order (Day/Month/Year)
I want to count the unique values in Colomn O: Ordernumbers
This is what I already have:
=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4;IF(Data!$D:$D="BOOKED";Data!$O:$O));Data!$O:$O)>0))
This formula is working. He counts the unique values in colomn O that meet the 2 criteria (Status = "BOOKED"; Customer = A4).
But I don't know how the add the date range in this formula. I want him to take only the values that are between 01/10/2012 and 30/09/2013.
I have a group of employees who work different divisions and work is not done everyday. I want to count the unique values of a division (only once) per day that the division shows up. Within a week, workers in a division might work 3 days and not other days. I've tried a few array formulas but they count unique within a range and I need the unique value to be based on the selected cell.
Please see attached : Unique Values In Between Dates.xlsx
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
I have a list of football fixtures that I want to pace in date order. The fixtures are however linked to a grid which has the following formula =IF(FIXTURES!C1="","",FIXTURES!C1 )
so that a zero doesn't appear until a result is actually added. Unfortunately when you sort the fixtures into date order the grid still looks at the original square, NOT the moved one appertaining to the fixture.
I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:
=COUNT(1/FREQUENCY(IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000)),IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000))))
=SUM(IF(FREQUENCY(IF('XYZ Activity'!C$4:C$5000=A4,IF('XYZ Activity'!G$4:G$5000="Yes",MATCH('XYZ Activity'!B$4:B$5000,'XYZ Activity'!B$4:B$5000,0))),ROW('XYZ Activity'!B$4:B$5000)-ROW('XYZ Activity'!B$4)+1),1))
I'm working on a workbook to track staffing patterns. I have two userforms included in my workbook. The first userform (userForm2) initiates upon opening the workbook. It's intended to allow the user to enter a date range and an office location for the report. The second userform (userform1) initiates when a command button (Weekly Summary) is clicked. I've linked texts boxes in userForm2 to cells in a hidden worksheet; this is where I'm holding the dates and office location until userform1 is initiated. I also have labels in userform1 linked to the same cells in the hidden worksheet so that when userform1 is initiated the office and date range appear at the top of userform1.
Here's the problem, when I click command button "Weekly Summary" the office location shows up perfectly however, the labels I have linked to cells in the hidden worksheet that contain dates do not update (i.e. they show the dates that were previously in those specific cells. I have to close userform1 and re-open it to get the dates to update.
I need the user to be able to choose a date range and office location when they enter the workbook. Then, I need userform1 to show the date range and office location (without having to open it, close it and re-open it) that the user chose on opening the workbook.
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 ?
View 14 Replies View Relatedeither writing a formula or coming up with some way to filter out certain values and it's stumping me... it's probably way more simple than I'm making it I've got an attendance spreadsheet with employee names going down the first column and dates going across the top row, managers use codes to indicate sick, personal time, etc.
There is a separate spreadsheet for supervisors to track vacation (don't ask me why) and I was asked to link the data from that spreadsheet into the attendance one described.
Linking is no problem, since it's the beginning of the year I'll just do a copy & Paste special.... paste link and they can still use that 2nd vacation spreadsheet to update both places for vacation time. However.... the vacation sheet has some extra info they don't need/want to see on the attendance one (ie. Lv @ 12:30, Holiday, etc.) How can I filter that out so that the only thing that appears is the V for Vacation that is linked & other attendance codes that are entered (S for Sick, etc).
I'm using Excel 2007
I have the following details
A B
1 Jim 3rd
2 Jack 5th
Now in the new cell i need A1 + B1 with / in between
Like Jim/3rd
Jack/5th
I have created a userform in excel with a listbox that has the names of 4 different sheets located within that workbook. I would like for whenever a name of the sheet is highlighted in the listbox, that sheet is opened. I have played around with the listbox.value and if then statements and can't seem to get it to work.
View 2 Replies View RelatedI am using Jon Peltier's worksheet_change event code to try and automate chart axes.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$AG$5"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$B$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$AG$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$L$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$N$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$AH$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub
However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
to make the user experience better by inserting the " Stock Search" sheet to speed up the process. The spreadsheet attached is a simplified version of the current version, where it would normally have many many more sites, areas and models of cars. The existing process means the user has to trawl through the sheet where they are only interested in what sites in their area has stock. I can not change the format of the "Red" and "Yellow" sheets so hoping to insert another sheet to do the work.
I am hoping to have on the "Stock Search" sheet a few drop down boxes.
The user will select
1. Area
2. Type of Colour (the type of colour selected should link with the sheet name in effect, so only stock within the "Red" sheet will be shown if the "Red" colour is selected from the drop down box)
3. Model of Car
This will then give the resulting Store/Stores with stock (value 1 or above) with those conditions met which is showing in the relevant sheet
So as per the attached sheet, if a user selected Area "South West", "Red", "Renault Megane"
then SO10 Clifton 1 and SO15 Yeovil 3 would be shown in a list.
I have searched through a lot of other posts, but I can't seem to make the recommendations work for my given scenario. I have a sheet that compiles information for different dates (based on a query that is run by the user), and in column H I have a start time that is listed in this format "m/d/yyyy h:mm."
What I need to do is find all of the unique dates "m/d/yyyy" from this given range, and return them in column O.
For example, I will have the following information:
Start Time
4/17/2008 17:19
4/17/2008 17:30
4/17/2008 17:31
4/17/2008 17:34
4/18/2008 17:23
4/18/2008 17:24
4/18/2008 17:26
4/18/2008 17:26
4/19/2008 7:43
4/19/2008 7:43
4/19/2008 7:47
4/19/2008 7:47
4/19/2008 7:47
4/19/2008 7:48
4/19/2008 7:48
and I need to have the following appear in Column O of my spreadsheet:
4/17/2008
4/18/2008
4/19/2008
There may be as little as one unique date, but there I don't want to set a limit of how many can return as it is based off of a user query.
I have a number of spreadsheets in a folder called country (each sheet is the name of a region). These sheets are linked to another sheet called master which is in another folder.
The regional sheets all have a list of wrap codes and pull details specific to each code from the master sheet. All wrap codes for all sheets are stored in the master, details for wrpas are manually entered here as well as any other associated information.
The link is kept by way of an index match formula used to populate information from the master for all wrap codes in the regional sheet.
Each of the regional sheets act as a report for a specific region and the master is the main source. Every week we need to open the regional sheets (some reside in different folders/subfolders) update the index match formulas in the sheet so that the data is refreshed and then save as a new file with data as values to another folder for reporting.
I am looking for a code that can automatically update all of the excel sheets in a given folder and then save them to a new folder automatically.
I have very very limited knowledge of VB (did make a hello world dialog box once) and am stuck as to any way to automate this process.
I have an excel file with 1723 entries. One of the fields is DATE. I have it sorted by DATE. I need to count the number of entries per unique DATE.
View 1 Replies View RelatedThe question is presented and explained within the attached WB.
View 4 Replies View RelatedI've come across a formula that I can't seem to get my mind fully around. I have a time-tracking sheet for employees that includes, among other fields, DATE and MILEAGE.
In my particular spreadsheet, DATE is from A2:A31 and MILEAGE is from G2:G31. The date column is set to the correct data type (date) and mileage is a general field and only has integers entered into it.
What I would like to do is count the number of unique dates on which mileage has been entered (field is not empty). If the total mileage for a day is less than 20, then do not count it. The reason for this is because an employee is entitled to be compensated for the total mileage of all service calls of a particular day minus 20 kilometers. If an employee, for whatever reason, enters say, 5km for the day, they should not be deducted 15km as would happen if the date was counted.
For example, if Jack traveled a total of 500km spread over 5 days, he is entitled to be compensated for 500 - (20 * 5) = 400km. The trick here is that if Joe has time billed on 6 unique days but only entered mileage on 5 of them, the formula still needs to return 5.
Here is what I have so far:
Code:
=SUM(G2:G31)-(20*SUM(IF(LEN(A2:A31),1/COUNTIF(A2:A31,A2:A31))))
This formula sums up the total mileage traveled and then subtracts 20 * number of unique days. The problems with it is that it still counts days on which no mileage was claimed (corresponding mileage field is empty). In addition, if the total accumulated mileage for a day is less than 20 then it is also still counted.
sorting list with date ranges with formula.
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
I have searched for a 'simple' solution to this but I only ever seem to find complex and indepth examples.
I summarise web hit information then chart it so I can look for trendlines. I get good info from the stat company so I can see where folk are coming from, search engine, weblink etc etc.
Once I have summarized it I have date running along the column headers then it is broken down through the rows so I have
Total Hits
Total Paid Advertising
Total Search Engine
And so on for my rows
As the days go on the data gets harder and harder to read so I was hoping I could use some kind of OFFSET function to select my data. So if my dates run from 2/11/07 to today I would like to perhaps say to Excel that I might want to see 20/11/07 to 20/12/07 in my chart.
I have plenty space to put the start and end date and I have a basic block of data.
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
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".
The sample sheet has been attached for reference.
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
My system is Windows 8 Excel 2010.
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 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).
Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.
For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
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.