merge data from duplicate rows

Excel Excel 2007
July 25, 2010 at 02:34:15
Specs: xp professional, 2gb
i hope someone can help me out here....

i have an excel sheet containing column A and Column B
the data in column A keeps duplicating i need the data to get merged from the b column into one row examples
machine Status
machine1 AD
machine2 sccm
machine1 sccm
machine3 sav
machine2 sav
machine2 sav

i need this to be combined in to one row

machine status1 status2 status3.....
machine1 AD sccm
machine2 AD SAV sav
machine3 SAV

can this be solved over a vbscript cuz i have the data in 10,000 rows

See More: merge data from duplicate rows

Report •

July 25, 2010 at 06:10:29

One solution is to use a User Defined Function.
This UDF is called xtndVlookup. I wrote this function a few years ago and it is not specific to your issue. It's main purpose is to allow a VLOOKUP-like function but data can be returned from columns to the left or right of the search range, and the search can be for any instance of the search item. In this case successive uses of xtndVlookup, return data from rows with the first, second, third etc. instance of say "machine1".

The advantage of this over a VBA macro is that it updates as soon as your data changes - you don't have to re-run a macro.

As you don't know how many instances of say "machine1" there are, the xtndVlookup function is wrapped in a COUNTIF function, so that an empty cell is returned if we are trying to look for more instances of say "machine1" than there are.

In Excel 2007, save your workbook in a macro-enabled format (xlsm, I think - I don't have Excel 2007 on this PC, so I can't check it out)
Right-click the worksheet name tab and select 'View Code'
In the Visual Basic window that opens, look in the Project Explorer pane (usually on left), find VBAProject(Your.xlsm)
(If the Project explorer pane is not visible, select View from the Visual Basic menu bar and click Project Explorer).
Right click on the name of your file, VBAProject(Your.xlsm) and select Insert, then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window:

Public Function xtndVlookup( _
                    SearchFor As Variant, _
                    SearchRange As Range, _
                    ReturnRange As Range, _
                    Optional SearchInstance As Integer = 1, _
                    Optional VertOffset As Double = 0, _
                    Optional MatchCase As Boolean = False, _
                    Optional MatchWhole As Variant = True, _
                    Optional Address As Boolean = False _
                ) As Variant

Dim blnErr As Boolean
Dim rngFind As Range
Dim strFirstAddr As String
Dim rngAfter As Range
Dim varDirec As Variant
Dim blnFound As Boolean
Dim dblRow As Double
Dim n As Integer

'convert MatchWhole
If MatchWhole = True Then
    MatchWhole = xlWhole
    MatchWhole = xlPart
End If

'set error flag to 'no error'
blnErr = False
'set found flag to 'not found'
blnFound = False

'test if Vertical offset is valid (within application valid ranges)
If SearchRange.Offset(SearchRange.Rows.Count, 0).Row + VertOffset < 1 Or _
            SearchRange.Range("A1").Row + VertOffset > Application.Rows.Count Then
     blnErr = True
End If
If blnErr = True Then GoTo ErrEnd

'test if SearchInstance is valid
If SearchInstance = 0 Or SearchInstance > SearchRange.Rows.Count Then
     blnErr = True
End If
If blnErr = True Then GoTo ErrEnd

'test that return range is same number of rows as search range
If ReturnRange.Rows.Count <> SearchRange.Rows.Count Then
     blnErr = True
End If
If blnErr = True Then GoTo ErrEnd

With SearchRange
    'setup direction specific parameters
    If SearchInstance > 0 Then
        'search is forewards
        'set address to search after last cell in range
        strFirstAddr = SearchRange.Offset(SearchRange.Rows.Count - 1, 0) _
                        .Resize(1, 1).Address
        'set search direction
        varDirec = xlNext
        'search is backwards
        'set address to search after as first cell in range
        strFirstAddr = SearchRange.Range("A1").Address
        'set search direction
        varDirec = xlPrevious
        'negate instance for loop counter
        SearchInstance = -SearchInstance
    End If
    'start the find process
    Set rngAfter = Range(strFirstAddr)
    'use Find to setup parameters - and to search for a single instance
    Set rngFind = .Find(SearchFor, _
                    After:=rngAfter, _
                    LookIn:=xlValues, _
                    LookAt:=MatchWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=varDirec, _
    'test that there is at least one instance
    If rngFind Is Nothing Then
        'not found
        blnErr = True
        'save address of first instance
        strFirstAddr = rngFind.Address
        'test if we just wanted the first instance
        If SearchInstance = 1 Then
            'flag found
            blnFound = True
        End If
    End If
    'looking for instance >1
    If blnFound = False And blnErr = False And SearchInstance > 1 Then
        'setup next cell to search after
        Set rngAfter = Range(strFirstAddr)
        For n = 2 To SearchInstance
            'find next
            Set rngFind = .Find(SearchFor, _
                            After:=rngAfter, _
                            SearchDirection:=varDirec, _
            Set rngAfter = rngFind
            If rngFind Is Nothing Then
                blnErr = True
                ElseIf rngFind.Address = strFirstAddr Then
                'not found
                blnErr = True
                blnFound = False
                blnFound = True
            End If
            'get out of loop if no more matches found
            If blnErr = True Then Exit For
        Next n
    End If
End With

'test if found
If blnFound = True Then
    'appropriate match found
    'calculate return range row
    dblRow = ReturnRange.Range("A1").Offset(rngFind.Row, 0).Row _
                    - ReturnRange.Range("A1").Row + VertOffset
    'test if calculated return range row is valid
    If dblRow < 1 Or dblRow > Application.Rows.Count Then
        blnErr = True
    End If
    If blnErr = True Then GoTo ErrEnd
    'return value using offsets, or address if flagged
    If Address = False Then
        'find offset value
        'Return range provides Column
        'Row is found row offset from top of Search Range plus any Vert Offset
        xtndVlookup = ReturnRange.Range("A1") _
                    .Offset(rngFind.Row - SearchRange.Range("A1") _
                    .Row + VertOffset, 0).Value
        'return address
        xtndVlookup = "'" & ReturnRange.Worksheet.Name & "'!" _
                    & ReturnRange.Range("A1") _
                    .Offset(rngFind.Row - SearchRange.Range("A1") _
                    .Row + VertOffset, 0).Address
    End If
    'no match so return #NA error
    xtndVlookup = CVErr(xlErrNA)
End If
Exit Function

'known error end
'return #REF error for captured parameter errors
xtndVlookup = CVErr(xlErrRef)
Exit Function

'error handler
xtndVlookup = CVErr(xlErrValue)
End Function

Click Save from the Visual Basic menu bar.
Click Alt+f11 to return to the main Excel window.

If your data is in cells A2 to B20000
then create a list of 'machine names' in column D starting at D2
In cell E2 enter this formula:
=IF(COUNTIF($A$1:$A$20000,"="&$D2)>=COLUMN()-4, xtndVlookup($D2,$A$1:$A$20000,$B$1:$B$20000,COLUMN()-4,0,FALSE),"")

Note that if you don't start your first formula in column E, you will need to adjust the -4, so that column number -x is One
(Column E is column 5, so 5-4=1)

The parameters for Vlookup are:
SearchFor - the item to be found
SearchRange - the range to search in
ReturnRange - the range to return data from
Optional SearchInstance - default is 1 (in this example the column number id used to create instance values that increment when the formula is extended horizontally across columns)
Optional Vertical Offset - default is 0 (use this to return the value before or after the matching item) - this example accepts the deault value of zero
Optional MatchCase, default is False
Optional MatchWhole - matches part of text in cell or all of it. Default is True (i.e. match all)
Optional Address default is false. If true, returns the address of the found item rather than its value

The formula in D2 is now dragged to extend it across as many columns as necessary - to display all possible values for the 'machine' - you can extend further to accommodate future additional entries in columns A & B
Now select the whole row of formulas D2 to xx2 and drag them down as many rows as required, beside the list of machine names.

This was what I got:
Source data:

	A		B
1	machine		Status
2	machine1		AD
3	machine2		sccm
4	machine1		sccm
5	machine3		sav
6	machine2		sav
7	machine2		sav2
8	machine2		AD

Output table:
	A		B		C		D	E
1	machine		statuses			
2	machine1		AD		sccm		
3	machine2		sccm	sav		sav2	AD
4	machine3		sav			


Report •
Related Solutions

Ask Question