Extracting Of Duplicates
Oct 18, 2009
i have this situation There are names( in column A) which have relating data in columns B (has dates) , and C ( codes ), it is an example - Bill(column a), 22.02.2009(b), 08(c).The names in column D have duplicates and non- duplicates in the column A .
When the name in the column D has several duplicates in the column A and every his duplicate has different data in column b and c , is it possible all of his duplicates go to the column E , however together with their data in column b and c ,( data from b go to f column and data from c go to g column..
Visually , it should look like something like that
namesdatescodesnamesbill21.09.2009f45billbill21.09.2009f45bill 12.10.2002g67bobbill 12.10.2002g67bob14.07.2001k90bob14.07.2001k90bob12.08.2001d45bob12.08.2001d45joan 07.05.2003d12
View 9 Replies
ADVERTISEMENT
Nov 26, 2007
1. Identifies the duplicates and cuts and pastes them into a different sheet
2. Deletes the "email to friend" link (not showing up on the sheet here) Its a javascript button. This button only is on random ones, not all of them.
3. Changes the layout of all the text to horizontal ie name, title, email, url, company.
*
A
1Eric Smith*
2Title:*
3Director of Marketing and Public Relations*
4Email:*
5eric@abccompany.com*
6URL:*
7http://www.abcompany.com*
8Company:*
9ABC Company*................
View 9 Replies
View Related
May 16, 2006
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:
$ % * !
View 8 Replies
View Related
May 23, 2013
I have a name in Column A, and a number that goes with it in Column B. Some of the names on Column A are duplicates. I want to extract a new list that will add the values in Column B of all matching names on Column A, so that my list has only unique names and a total of the numbers in Column B.
A:A B:B
SPP-001 2
SPP-002 3
SPP-006 4
SPP-001 5
SPP-008 9
SPP-006 3
Result that I'm looking for"
C:C D:D
SPP-001 6
SPP-002 3
SPP-006 7
SPP-008 9
View 6 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Aug 24, 2011
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).
View 3 Replies
View Related
Mar 23, 2014
I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.
Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.
On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.
And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.
Is there a formula to count these as one weld:
1692-SG-0040-04_05
1692-SG-0040-04_05R1
1692-SG-0040-04_05R2
This as one weld:
6317-FG-1690-02_06
6317-FG-1690-02_06C1
6317-FG-1690-02_06C2
...and this as one weld:
1698-SG-0077-01_04
1698-SG-0077-01_04C1
1698-SG-0077-01_04C1R1
View 7 Replies
View Related
Jan 24, 2012
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.
View 2 Replies
View Related
Apr 8, 2014
I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
View 7 Replies
View Related
Oct 14, 2008
I am trying to extract the last name in a column and put it in a seperate column. Here is what my data looks like:
Joe B Smith
Jan Doe
I want to take out Smith and Doe. I found this formula on a post =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)), but it just returns B Smith.
View 3 Replies
View Related
Sep 22, 2007
I tried the =min(sheet1!B:B) but it pulls up the min of my entire column i need the min of each row This is what i got going on except there is 150 rows on this sheet. the rows are set with conditional format to highlite the min and Max of each row (which i cant show here!) ...
View 10 Replies
View Related
Jan 5, 2009
I have two exported lists of data to compare from my newsletter program. One is the list of email addresses that I sent the newsletter to and the other is a list of the bounced email addresses.
Question: How can I extract the bounced emails from the list of sent email addresses?
In other words I want a list of clean, sendable email addresses.
View 10 Replies
View Related
Jun 2, 2009
How do you remove the last 2 digit
A B
12345688 88
12548963 63
14552365 65
So in column B is would show
88
63
65
View 3 Replies
View Related
Aug 15, 2009
how to extract this two dates (sample below) in separate cell and format it as like this: "January 1, 2009". "12/1/08 thru 1/1/09" and another question, is there any way, that i can make it as "JANUARY 1, 2009", it seems upper function is not working with dates.
View 4 Replies
View Related
Jan 29, 2013
I am calculating a range of results and I'd like to extract the values calculated and put them into a seperate spreadsheet then re-use to formulas to calculate my next set of results. Obviously copy+pasting from my formulas just copies the original formula and not the result of it.
View 2 Replies
View Related
Sep 23, 2013
I know that there are programs out there that will convert a PDF into excel, but what I need to know is, is there a way for excel to access values (numbers) in a PDF and then use those values in a worksheet?
So, if I have a table in a PDF file - containing part numbers, descriptions and quantities ordered, I want to be able to extract the part numbers and QTY, and have them populate an excel table?
View 11 Replies
View Related
Jan 12, 2009
extract the values from a field and inserting as column.
I have attached the workbook, with the output worksheet. The output sheet which is what i required as result.
I need to extract set of word between "/" and insert it in the exact column name, if any column name is not there we need to create that column and insert that word corresponding to that row and column.
View 2 Replies
View Related
Feb 4, 2006
i need a formula to extract numbers.
if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.
View 14 Replies
View Related
Aug 8, 2007
How do I extract the first word of a cell contents?
For example, cell A1 has "John Smith"
I want cell B1 just to show "John"
Obviously, the first word would be of varying lengths, so I'm guessing the formula would need to find the space.
View 14 Replies
View Related
Nov 29, 2011
I have a file tens of thousands of lines long. This has a bunch of store numbers along with the amount owing to them.
Now this is presented like this
Str 50 $10
Str 50 $20
Str 50 $15
Str 60 ... etc etc
I just need the totals for each store, which I will then copy into my billing file. The person who sent me this original has already subtotaled it, and each site has a plus sign next to it. When I click the plus sign I can see the breakdown (ie store 50 ten times, store 60 twelve times etc) but otherwise the store totals are given on 'adjacent' rows. The problem is these rows arent actually adjacent, the other rows are just hidden. So when I try to copy the store totals all the other junk gets copied as well.
How do I copy just the store number and totals?
View 6 Replies
View Related
Mar 23, 2007
I have this data in cells A1-A7 (each line in the example below is a cell with the cells name to the left of the: and the value to the right of the
The colors are simply to show the matching data----there are no colors in the excel sheet
A1:Nordica Valley
A2:8888 bla oook nikk Rd $15,950,000
A3:Sumik/Arnov 120.443.8976/555.443.8976
A4:5+3. 13+ acre horse ranch in
A5:private canyon. Pool, barn/ more.
A6:Robinson 999.443.8976
A7:Cell7 is blank
A8:Jit/Song 587.678.6788
I wish to extract the Phone#'s and Names to columns B&C.
The data for this example would be broken down as follows:
B1:Sumik
C1:120.443.8976
B2:Arnov
C2:555.443.8976
D1:.5
D2:.5.........................................
View 10 Replies
View Related
Oct 2, 2008
The following code will return everything in the array. What I would like for it do is:
Case 0 - I want to return first two ie, "A" and "B"
Case 1 - I want to return "C" through "F"
Case 2 - I want to return everything else that are not in MyA
Dim MyA As Variant
Dim i As Long
MyA = Array("A", "B","C","D","E","F","G")
For i = 0 To UBound(MyA)
Select Case ListBox1.ListIndex
Case 0: Rng.AutoFilter field:=5, Criteria1:=MyA(i)
Set Rng2 = ws.AutoFilter.Range
Rng2.Offset(1).Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2)
ws.AutoFilterMode = False
End Select
Next i
View 9 Replies
View Related
Mar 2, 2009
I have a spreadsheet with multiple formulas and wanted to create a report to list out these formulas. Is there a way of extracting all formulas from a sheet?
View 9 Replies
View Related
Jul 30, 2013
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
View 1 Replies
View Related
Apr 23, 2014
I'm trying to extract information from a cell that I cut and paste from another source:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 441
Each cell is different in that it may be a different day or time:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 434
I want to make a formula that will extract the pertinent information (day of the week and time), and store it to another 2 cells, making a list as it "extracts":
Day
Time
Tuesday
8:00 - 9:15
Thursday
8:00 - 9:15
View 8 Replies
View Related
Apr 21, 2014
I have been trying to extract dates from strings.
The formula I have currently works however I've found that sometimes there are two or more dates in the string (at which point I'd like to extract the last date (appointment date)).
View 2 Replies
View Related
Aug 21, 2014
In column A I have the following numbers
13710
14782H
9827
14782
14206B
a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M
View 6 Replies
View Related
Dec 26, 2013
I have a cell that contains values such as "AAPL" or "0001.HK" (without the quotes). I would like to extract the content to the left of "." (if any). i.e. I want the cell to return "aapl" or "0001". What formula should I use?
View 9 Replies
View Related
Jan 1, 2014
I'm trying to extract the first name from each row in column a using:
Formula:
A B
Joe Smith Joe
Julie Smith Julie
Julie #VALUE!
If the cell does not have 2 names it's giving me an error.
View 10 Replies
View Related
Feb 17, 2014
i have 8 digit invoice numbers. Each 8 digit number starts with one of three 4 digit ID codes. Depending on the first 4 digits, I want a different "company" to show up.
First seems to work fine.
=IF(E6>"1NCC","North Carolina",IF(E6>"2ALA","Alabama",IF(E6>"3SCC","South Carolina")))
View 3 Replies
View Related
