column duplicates / merge data formula and/or macro???

dazedays

Beginner

Points

45

Posts

3
Jul 14th 2005
- #1
I am working with a database that contains the following information

(This the the data array)

NAME | NUMBER | TYPE

In the name column, there are duplicate names.

In the "type" column, they're are multiple types:

"WORK,HOME,FAX,EMAIL,RADIO,OTHER"

My dilema is, I'm trying to create a formula so that the following happens.

I want to have Excel sort by "type," then transition the "numbers" from the original column "type" to new columns equal to their "type"

Example:

(Original Data)
NAME | NUMBER | TYPE
Jeff | 1234567 | HOME
John | 7654321 | WORK
John | 8765432 | HOME

(After Macro or Formula or Both) I'd also assume on "Sheet Two"

NAME | NUMBER | TYPE | HOME | WORK | FAX | OTHER | RADIO |
Jeff |............|............|1234567 |.............|...........|............|............|
John |............|............|8765432 |7654321 |...........|............|............|

As you can see the formula, and/or Macro automattically determines three things:

1. It locates the data from column 2 and places it into the proper column whereas the data in column 3 = header row of column(s) 4-8.

2. If the "type" data matches the header text, the data in column 2 moves to the the correct column.

3. If there is a duplicate name in column 1, the formula will enter the data into the proper column, but will enter the data into the topmost duplicate named row, and then when all data from column 2 is moved, it will delete the extra duplicate rows.

I'm going crazy trying to get this done so that excel can automate this time consuming process.

I get lists 600+ and I need to reorder them according to the above format, it
would be extremely helpful if one the gurus on this site could help me with some general or advanced formulas!

Thanks,

Jul 14th 2005

#2

Re: column duplicates / merge data formula and/or macro???

Will this work?

Code

Sub DazeDays()
'Written by Barrie Davidson
Dim WorkingSheet As Worksheet, NewSheet As Worksheet
Dim DataRange As Range, NewRange As Range
Dim EvalRange1 As String, EvalRange2 As String


Application.ScreenUpdating = False
Set WorkingSheet = ActiveSheet
Set DataRange = Range("A1:C" & Range("A65536").End(xlUp).Row)
Sheets.Add
Set NewSheet = ActiveSheet
DataRange.Resize(, 1).Copy Destination:=NewSheet.Range("A1")
Set NewRange = Range("A1:A" & Range("A65536").End(xlUp).Row)
NewRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), _
         Unique:=True
Columns("A:A").Delete Shift:=xlToLeft
Set NewRange = Range("A1:A" & Range("A65536").End(xlUp).Row)
Range("B1").Value = "Number"
Range("C1").Value = "Type"
Range("D1").Value = "Home"
Range("E1").Value = "Work"
Range("F1").Value = "Fax"
Range("G1").Value = "Other"
Range("H1").Value = "Radio"
EvalRange1 = "'" & DataRange.Worksheet.Name & "'!" & DataRange.Resize(, 1).Address
EvalRange2 = "'" & DataRange.Worksheet.Name & "'!" & DataRange.Offset(, 2).Resize(, 1).Address
NewRange.Offset(1, 3).Resize(NewRange.Rows.Count - 1, 5).Formula = _
    "=IF(SUMPRODUCT((" & EvalRange1 & "=$A2)+0,(" & _
    EvalRange2 & "=D$1)+0,ROW(" & EvalRange1 & "))=0" & _
    ",""n/a"",INDIRECT(""'" & DataRange.Worksheet.Name & _
    "'!B""&SUMPRODUCT((" & EvalRange1 & "=$A2)+0,(" & _
    EvalRange2 & "=D$1)+0,ROW(" & EvalRange1 & "))))"
NewRange.Offset(1, 3).Resize(NewRange.Rows.Count - 1, 5).Copy
NewRange.Offset(1, 3).Resize(NewRange.Rows.Count - 1, 5). _
    PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True


End Sub

Display More

jimtons

Beginner

Points

5

Posts

1
Apr 5th 2011
- #3
Re: column duplicates / merge data formula and/or macro???

HELP.
I have a spread sheet where in column A1 I have duplicate numbers. To the right I have the associated components of that number and need to be left unsorted. My goal is to create a macro that will take all the duplicates merge them and all other columns are left as is. This spread sheet will be used for a mail merge data base to fill out forms. On the forms I will have fields that will be filled out by the unique numbers in column A1 and in the body of the form the data to the left of Column A1 will be placed in the body of the form. Is there anyone that can help me?
Herbds7

Professional

Points

4,160

Posts

832
Apr 5th 2011
- #4
Re: column duplicates / merge data formula and/or macro???

Excel 2010 PivotTable, Tables
Pseudo text PT.
With macro, no formulas.
http://c3017412.r12.cf0.rackcdn.com/04_05_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.