I am conducting a stock count.
Column A consists of a list of the barcodes I've scanned.
In column A there is sometimes more than one of the same barcode when i have more than one of the same product. is there a way of deleting duplicate barcodes in column a and replace them with a 'Quantity' column?
I am having trouble creating a function to count duplicates of duplicates.
An example of the data table 1 is:
Product 1 2nd
Product 1 2nd
Product 1 New
Product 1 New
Product 1 Flt
Product 2 2nd
Product 2 New
Product 2 New
Product 2 Flt
Product 2 Flt
Product 3 2nd
Product 3 2nd
Product 3 2nd
Product 3 New
Product 3 Flt
I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:
New 2nd Flt
Product 1 XX XX XX
Product 2 XX XX XX
Product 3 XX XX XX
I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.
I am using Windows 7 and MS Office 2010.
I have some cell styles that refuse to be deleted in Excel 2010. I can see them on the ribbon, but right clicking and deleting doesn't do anything. Any way I can get rid of them? Just makes things look very messy. If I click them it says "Style 'XXX' not found."
View 3 Replies View RelatedI'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.
Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete
user of excel 2010.
I have a spread sheet with 2 columns that I have applied conditional formatting to to find duplicate values. So the duplicate cells are highlighted in red now.
Problem - I want to copy only those red cells to a new column in a new tab, and am having a devil of a time figuring out. Best case would be some sort of formula I could use on the new tab&column to do this.
I have a problem where Excel 2010 is deleting one of my conditional formatting conditions. I do not know if it is deleting it on open or close. I just know that I enter the conditional formatting, save the workbook, close the workbook, reopen the workbook, and it is gone.
The other conditional formatting conditions (simple "Cell is Blank") are left intact.
Setup:
Cell with conditional formatting is on sheet "MyRecord" at "$E$7". A corresponding boolean value on separate sheet "Data" and must be referred to by using "OFFSET" with a record locator value (workbook level named range "RecordLocator") that leads to corresponding data row, so looks like following:
Code:
=IF(OFFSET(Data!$D$5,RecordLocator,0),TRUE,FALSE)
When I configure this, it works great until I save and close the workbook. When I reopen the workbook, this particular conditional formatting is gone with all other conditional formatting left intact.
I've tried using the actual range address in place of range name "RecordLocator", changes nothing.
I can't figure out why Excel deletes this particular conditional format.
Is there a way to differentiate the number of unique and duplicate entries (considering multiple criterias) using a formula.
I am finding it hard to describe the request hence i have attached a spreadsheet for reference.
The first tab contains the data where I want the result in 'column J'
The second tab contains the metrics of how to calculate the result.
I am sure this will be difficult to understand at first hence feel free to ask as many questions you would like to [only related to the subject ]
For the record, I use Excel 2007 & 2010.
I have following sheet:
Data from A1: BU1644
I need to check, if in A1:BU1 are any duplicate words.
All the formulas I found deal with finding duplicates downward (like A1:A1000).
Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
How to set up a formula on Excel 2010 using Conditional Formatting.
I want to identify duplicate account #'s within 15 days of the work date. Here is sample of the report.
Account Number
Facility
FC
Payor
Current Balance
Aging Level
Team Member
Work Date
87890
HHSS
1
BCBS OF TX (PPO)
$9,616.94
121+
Steven Johnson
5/20/2013
[Code] .........
I have a data in sheet1 as below :
Excel 2012ABCDE1Sr. No.PriceSr. No.Price
21234535123453531234623123462341234712123471251234818123911861
234919135011971235023412350234812371251235125912352261235226101
2353271235327111235428123982812123552912355291312382301235630141
[Code] .........
And now looking for output in sheet2 as below :
Excel 2012ABCD1Sr. No.Price1Price2Total
21234535357031234623234641234712122451234818#N/A1861234919#N/A
1971235023423446881237125#N/A259123522626521012353272754111235428#N/A
[Code] ........
The sr.no. codes and price will keep changing in sheet1, it should automatically update in sheet2. Is it possible with either vba or formula?
I have attached sample files as reference. I am using excel 2010
I wish to move all duplicates from one sheet (Orders) to another(Duplicates).
Sheet0 is the original sheet(Orders)
Sheet1 is the orders sheet once the duplicates have been removed
Sheet2 is the duplicates sheet
Sheet3 I am also looking to achieve what is in sheet3 so that the quantity and stock code is all in one row as shown in the example sheet3
Basically I am looking for it to finish up like sheet1 and sheet3
Link to files: [URL] ........
I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.
So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.
Here is the working simple query to retrieve a single row:
Code:
dupeSQL = "SELECT PSID FROM [Obstructed$] "
Here is the working single query to retrieve a count:
Code:
dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "
When combined, I cannot figure out why it won't work:
Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "
Here is what I am trying to get to:
Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] " _
& "WHERE COUNT(PSID) > 1 " _
& "GROUP BY PSID"
I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.
Excel Version : 2010
Attached File name : <Eliminating duplicates.xlsx>
I want to eliminate the duplicates and plot without duplicate in the yellow cells.
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code:
=MID(C2,1,9)
and in source M:
Code:
=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)
This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code:
=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
I am trying to create a 100% stacked column which has a superimposed line chart over the top of it.
Basically % of yes and no for two separate datasets, but which share the same characteristics. The x-axis is months of the year.
CSP Yes
CSPNo
All Yes
All No
Apr-13
6
19
[code]....
I can create 2 separate charts. One for the yes/no of one set as a 100% stacked column. I then use 'no fill' on the No column to stop this from being seen.
I can create a 100% line chart for the second data type, and 'no fill' the 100% line that appears.
However, I can't merge the two charts to show the Yes % for one dataset as bars and the Yes % of the other as a line.
I am using Excel 2010 on XP.
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies View RelatedI have a large spreadsheet of over 5,000 rows and 20 columns. What I would like to be able to do is find a formula(?) that will enable me to retain rows of data when Cells in column B and column E contain duplicate data but delete entire rows if the contents of cells in column B and E are not duplicated.
For example:
Column B contains Part Numbers
Column E contains Manufacturer
If a part number (Cell B)has been made by two or more manufacturers (Cell E), retain all relevant rows.
If a part number has only been made by one manufacturer, however many times, delete all relevant rows.
What I'm trying to be able to achieve is retain data so that I can analyse it when 2 or more manufacturers have produced the same parts.
sample3.xlsx
I'm still having issues with this workbook. I cannot use a pivot table to fix it, I don't understand them and it confuses me greatly..... so I'm at the mercy of either a formula or macro. I need to combine the duplicate part numbers (a), total the quantities, average the price (d), and total the amount of the part (e). I'm having a very difficult time with it.
(I have a sample attached that is file sample 2, and the entire spreadsheet attached- sample 3)
I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A). I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B. So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.
A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657
So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
Does anyone have a utility which can be used to select a specific column i.e. B:B, which then 'selects' all duplicate values in that column prior to deleting them ?
View 4 Replies View RelatedI'm trying to remove duplicates without deleting the rows they were in (instead just being left with blank spaces).
For example Column A might look like this:
Column A
1
1
1
1
2
2
2
3
I need it to look like this:
Column A
1
2
3
How I can delete these duplicates without doing it one by one?
way to join two or more tables in excel adding new rows and columns.
In this way:
Input TABLE1
ID, Tag1, Tag2
1, alfa, big
2, delta, small
Input TABLE2
ID, Tag1, Tag3
2, delta, green
3, gamma, yellow
Output RESULTING TABLE
ID, Tag1, Tag2, Tag3
1, alfa, big, -
2, delta, small, green
3, gamma, -, yellow
I have some Job numbers that are duplicates. I can't just do a loop and delete any duplicates that come across because I have to be sure the right one is deleted.
I want the old data to be deleted if there is a duplicate..I just don't know how to verify which one is the old one using VBA. I was thinking of maybe using an advanced filter, but I don't know enough about them.
Is there code that will check both duplicates and delete the old one?
I have a report created daily with account numbers, however there are duplicates. Is there a faster way to filter out duplicates other than sorting and deleting manually?
View 4 Replies View RelatedI 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 two seperate lists. One on Sheet 1 and One on sheet 2. What I need to do is take the list on sheet 2 and compare it to the list on sheet 1. If any of the items appear on sheet one, I need to delete those items form sheet 2.
So in the end the only items left on sheet 2 will be items that dont match any items on sheet 1
I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....