Excel Excel 2007

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 savi need this to be combined in to one row

machine status1 status2 status3.....

machine1 AD sccm

machine2 AD SAV sav

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

Hi, 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 Else 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 Else '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, _ MatchCase:=MatchCase) 'test that there is at least one instance If rngFind Is Nothing Then 'not found blnErr = True Else 'found '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, _ MatchCase:=MatchCase) Set rngAfter = rngFind If rngFind Is Nothing Then blnErr = True ElseIf rngFind.Address = strFirstAddr Then 'not found blnErr = True blnFound = False Else 'found 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 Else 'return address xtndVlookup = "'" & ReturnRange.Worksheet.Name & "'!" _ & ReturnRange.Range("A1") _ .Offset(rngFind.Row - SearchRange.Range("A1") _ .Row + VertOffset, 0).Address End If Else 'no match so return #NA error xtndVlookup = CVErr(xlErrNA) End If Exit Function 'known error end ErrEnd: Err.Clear 'return #REF error for captured parameter errors xtndVlookup = CVErr(xlErrRef) Exit Function 'error handler ErrHnd: Err.Clear xtndVlookup = CVErr(xlErrValue) End FunctionClick 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 valueThe 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 savRegards

Ask Your Question

Weekly Poll