I have a list of line items and I want to be able to see how many duplicates in Column A and B.
Column A has text field and column B is a numeric field
I thought I could use INDEX MATCH but I'm unsure.
In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
I have attached a sample of what im trying to do here.
A2:A10 i have items, B2:B10 they can selected "yes" or "no" and C2:C10 the values
What I would like is, is "yes" was selected, i would like the item name added to a list. so basically:
=IF(A2:A10="YES",A2:A10)
And then I can just add the value to each item later but I need to remove duplicates and obv have this carried throughout the cells.
Attached File: TEST.xlsx
I need to add items to a combobox without adding duplicates or empty data. I am able to add without empty data but how to ignore the duplicates.
Here is what I have...
Code:
Dim GL As Range
Dim ws1 As Worksheet
Set ws1 = Worksheets("Main")
For Each GL In ws1.Range("MainGL")
If IsEmpty(GL) Then
'do nothing
Else
ComboBox2.AddItem GL.Value
End If
Next GL
I have 2 huge lists. One is " the Master list" which I have to extract items that are not already listed on the second list . I don't want to remove duplicate entries,I want to remove the duplicates completely. in both lists, so that I only have items that are not in the second list...
like this..
List 1:
# $ % & * ! @
List 2:
# & @
so I want to end up with:
$ % * !
I have an excel sheet with data where I have column A index number, B company name, c empty, d data , e with item.
[Code] ......
What I trying to do is I made a dropdown for company list and i select comp1 then i want to populate items list with out duplicates .
I have a movie list. column A is the number location i have my movie stored, and column b is the actual movie name. I backup my movies so that my kids wont destroy them. The list is around 1000. I have duplicate backups, i want to have a list of just the duplicate movie names and there locations.
View 12 Replies View RelatedOk, so basically I've been asked to create a chart that lists the top 3 types based on count per year. I don't want to make 4 charts, but my top 3 types differ by year.
Type would be column #1, and Count would be column #2.
Type Count
1 7
10 6
11 7
12 5
13 24
14 2
15 7
[Code] .....
In my code to filter on some data i'm trying to filter out the top 5 highest values.
[Code] ......
The code just fails to run properly.
I have a list on column a
i.e
Name
Bob
Jones
Michael
Smith
Bob
Jones
What I want is to only show duplicates, else "". One way is to sort column then use =exact function and deterimine true/false. however is there an easier way, hence withouth having to sort? i just want it to show only duplicate names in cell's.
I m trying to show duplicate entries and what forumla to use.
Have A1 through to A5000 listed with diffrent phone numbers, I have been supplied with new numbers which i have placed in B1 (Not quite 5000 worth) But there are duplictes in B1.
Have Column of cells 1500 total B4, B5 etc on sheet 2.
Wish to identify if any of the cells which have duplicate numbers to previous cells.
Going from top to bottom. (Cells have 10 digit numbers)
Identify duplicates in cells A4, A5 etc. sheet 2 by the word "duplicate"
Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?
I know filters can do top 10 but it doesn't consolidate similar items.
I have a section of code that takes SO long, but I have to have it. Bascially, I need a pivot field to be set to "All", but there HAS to be a faster way. Here's my
Sub FloorCompareSetter()
Dim pt As PivotTable
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables("PinPointPivot")
' Speeds up code dramatically
pt.ManualUpdate = True
'Set the floor comparison for managers, coaches, and reps
' Make sure all PivotItems along line are visible
For Each pi In _
pt.PivotFields("Manager").PivotItems
pi.Visible = True
Next pi
pt.ManualUpdate = False
End Sub
Auto Merged Post Until 24 Hrs Passes;Also, note that I have used other techniques to speed up the process:
With Application
. ScreenUpdating = False
.EnableEvents = False
.Calculation = xlManual
End With
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have a spreadsheet which shows information based on what a person has picked and how long it took them to pick.
What I now want to show is an extract which will show - in a separate area - what shift & person still has not completed the pick (completed time section remains blank) together with the type of product they are picking plus the 'operation number'.
My current spreadsheet is as follows;
[IMG]C:Documents and Settingschristine.lawsonDesktop[/IMG]
Table shows as follows:
SHIFT
PRODUCT TYPE
MSN
OP NBR
PICKER NAME
DATE PICKED
START TIME
COMPLETED TIME
OVERALL TIME TAKEN
AVG LINES PER HR
BLUE
T&F
4
001
FRED
6/11/12
17:55
18:40
00:45:00
4
[Code] ........
As you will see from the attached picture/table there are 2 lines which do not have 'completed' times shown. It is lines, such as these that I need to be able to show in a separate area.
I am filtering data using listbox (userform).
I want listbox show only names(without duplicates).
The setup of my worksheet looks like this:
Excel 2010ABCDEFG1Tch grpTchr Split27B-MA5AWAWAWAWAWAW37B-MA4CB1,JDCB1,JDJD,CB1MMF,JDMMF,JDMMF,JD47B-MA3SXGSXGSZBSZBSZBSZBTchGrps
A great chance to use the HTML Maker for the first time!
What I would like to do here is show how many of each value there is in the range.
So for 7B-MA5, it would say 6. For 7B-MA4, it would be 2 / 1 / 3, since there are 3 unique values and their counts respectively. Finally for 7B-MA3 it would say 3 / 3
A formula option would be great but I'll also take a macro option since it is a macro that generates these lists in the first place, so I could just add the new code to the end of it.
I have a list of items in column A. Column B has each item's net price. Columns C-N shows the consumption of the items per month. Column O shows in which location the items are stored.
I need to do a sumproduct so that it shows the value of the items retrieved from that particular location per month.
If it didn't have to be by location, I would've simply done something like =sumproduct($B1:$B10,C1:C10) and copied it across the columns. How do I tell excel to sum per location as well? I know there is a simple solution to this, just not seeing it...
EDIT: I tried =SUMPRODUCT(($B1:$B10)*(C1:C10)*(O1:O10=$B1)) but it gives a #VALUE error
I work at a trading firm and use pivot tables to report on the success of traders on a daily basis. I add daily trading data to a raw data table that powers a set of reports. In one report I want to view MTD stats for a filtered group of 10 traders. The issue is that if I add a set of daily data that includes a new trader name, it will automatically be pre-checked and added to this report (and this happens daily). The only solution I came up with is to add another column in the raw data table that would allow me to group these traders and then use a page filter to include only them. This will work but I'd rather avoid adding columns to an already unruly data table (and would like flexibility to periodically define and track an arbitrary set of traders).
View 4 Replies View Relatedi have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).
At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded...
Sub Button1_Click()
Range("B7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.PivotItems("10/30/2006").Visible = False
.PivotItems("10/31/2006").Visible = True
End With
End Sub
I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
I have a list of data that includes employee ID number in column A and a certain document that they have on file (onboarding docs such as I-9, W-4, copy of Social Security card, etc.) in column B. If one employee has 10 documents, they are listed with the same ID number 10 times in A1:A10 and the different document names are listed in B1:B10.
I want to clearly see which employee has which document by having the ID numbers going vertically in column A and then an X or check mark of some sort going across the rows.
The set up of what I would like filled in is in the sheet called "status" and the list of documents on file is in the sheet called "docs."check file.xlsx
I tried a combination of an IF and VLOOKUP with a MATCH function but I wasn't getting what I wanted.
I have 2 worksheets in which the worksheet named Existing Items has a table consisting of list of items with their unit price & worksheet named New Items also has a modified table with updated items (some parts might be added, removed, modified or unchanged). I have attached the test workbook (Sample.xlsx) here for your reference.
I am looking for a macro that compares these 2 sheets with the Part Number as a Unique Identifier & displays the result in the following manner:
New items added in New Items -> List these items in the Items Added worksheet Items removed from Existing Items -> List these items in the Items Removed worksheet Items whose price has changed in New Items -> List these items in the Price Change worksheet Items whose price has not changed in Existing Items & New Items -> List these items in the Unchanged worksheet A Summary worksheet which shows the count of items added, removed, modified & unmodified
Note that I have already included information in the Existing Items, New Items, Price Change, Unchanged & Summary worksheets just for your reference but this needs to be done by a macro.
seeattached data set.
Contract Item
Gross Profit
GP %
[Code]....
It is Gross Profit in Dollars and % for a particular project. Each row is a contract Item. The Overall Gross Profit for the month in dollars is 34,114.86 In preparing a short commentary to discuss this 34,114.86 I need to discuss atleast some contract items. If there were no negative results I have often in the past simply written. The top 5contributors to this Gross Profit were contract items 1 -6, out of 42, delivering 80% of the result. I might also go into what actually those contract items were and why they presented such a decent Gross Margin, ie. Costs came in well under budget(estimate). But with negative results, the bottom 4 contract items represent a large dollar loss of-34k which is 100% basically of the Gross Profit itself. If these had even just broken even we would have a GP double the current amount. So they obviously deserve as much comment.
Q1
In excel orsimilarly in say Crystal Reports what is a good way of further sorting these 42records either in a table or chart, whereby say the top 80% contract items that had a major impact on the gross profit are shown and the rest hidden. Ie. All thelittle sub 1000 or sub -1000 amounts.
Q2
How wouldother people here write a commentary on this data set? Can you script something to illustrate?
1) How do I get the three (3) items columns to be right underneath the ''Recovery Turnaround Days of Files Per Cheque'' heading
2) How do I get each of items #1 - 3 column, to have each their own color (that is not the same color as the '' Average Recovery Days '' column color) ie;
Item #1, 30 = I would like it to be say, Red
Item #2, 35 = I would like it to be say, Pink
Item #3, 30 = I would like it to be say, Yellow
3) How do I get the column under the ''Average Recovery Days'' heading to have it's own color scheme as well, such as as say the color ''Purple'' (or any other color that is different from all the other 3 columns).
how you go about putting the columns so they align right underneath the particular heading, that would be awesome. Also, how to have a color scheme for the ''Average Recovery Days'' to have it's own color (For the column under this heading to have it's own color, what ever color that is), that does not match any of the column colors in items #1 - 3 (under the heading ''Recovery Turnaround Days of Files Per Cheque''),?
I have two columns of data, I and M and need to find the cases where duplicates occure in I but the data in M does not match. If the data in M matches then it's ok. I have column I sorted A to Z.
View 1 Replies View RelatedI have a list of numbers in column A of my Worksheet, and a list of names in column B.
There are duplicates in both columns:
A B
234 John Smith
253 Charles White
461 Mary Carlsson
876 Erica Alvin
954 Joe Brown
234 John Smith
461 Mary Carlsson
The duplicates in column A and in column B are correct, because the same names correspond to the same numbers. I would like a formula or VBA macro, if possible, to detect when there is no correspondence, for example, if the last row above were
A B
461 Ben Wayne - where it should be Mary Carlsson.
Is it possible to check all the rows of both columns and identify errors?
In Col A, from A1:A12 I have
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
In column G from A1:A6, I have
6
9
7
11
15
How could I return an 'x' in column C against the A values corresponding with the G values, for example an 'x' in C6, an 'x' in C9, an 'x' in c15' etc? And a blank for example at C8 and C14.