vba - Excel macro to combine two excel rows by matching two columns -

how can match names 2 columns , if same merge 2 lines. meaning if first_name , last_name same combine rows (since presumably same person). if other cells in row same want them combine. if different, want both values/strings saved leaving them both in combined cell comma between them.

so this:

first  last     number   sign joe    white    1122     scorpio joe    white    1144     scorpio joe    jones    11445    leo david  white    112      virgo 

should turn this:

first       last        number       sign joe         white        1122, 1144  scorpio joe         jones        11445       leo david       white        112         virgo 

since first 2 lines have match between joe white , joe white (both first , last name same) 2 lines combined. since number column has different values, combined in 1 cell comma delimitation. because sign, in case scorpio, same gets combined without listing both (duplicate) values. in case of third , fourth name, 1 of names matches (either white or joe) not combined @ since both names have match.

ok dsine here suggestion. part of spirit of should demonstrate , share have tried @ moment. starting point write down in words how might go solving problem, try code , research gaps. see comments in code below possible example. if still stuck. , post question.

so starter ten follows:

sub concat()  dim sdrow long, sdcol long, ldrow long, ldcol long dim rowno long, resultrow long dim ws1 worksheet, ws2 worksheet dim keyval string  'assume original data in sheets("data") 'assume result data in sheets("data2") set ws1 = sheets("data") set ws2 = sheets("data2")  'original data block r/c sdrow = 2 sdcol = 1 ldrow = ws1.cells(rows.count, 1).end(xlup).row ldcol = ws1.cells(sdrow, columns.count).end(xltoleft).column  'assume result data set in sheets("data2") placed in same sheet position 'as in sheets("data") , copy headings ws1.activate ws1.range(cells(sdrow, sdcol), cells(sdrow, ldcol)).copy _ destination:=ws2.cells(sdrow, sdcol)  'sort original data  ws1.activate     ws1.range(cells(sdrow, sdcol), cells(ldrow, ldcol)).select     selection.sort key1:=columns(sdcol), order1:=xlascending, _         key2:=columns(sdcol + 1), order2:=xlascending, header:=xlguess, _         ordercustom:=1, matchcase:=false, orientation:=xltoptobottom, _         dataoption1:=xlsortnormal  'loop through original data rowno = sdrow + 1 resultrow = rowno  while rowno < ldrow     'test if names same     keyval = cells(rowno, sdcol) & cells(rowno, sdcol + 1)         if keyval = cells(rowno + 1, sdcol) & cells(rowno + 1, sdcol + 1)             'copy data row sheet("data2")             ws1.range(cells(rowno, sdcol), cells(rowno, ldcol)).copy _             destination:=ws2.cells(resultrow, sdcol)              'modify 'number' cell in sheet("data2") if required                 if ws1.cells(rowno, sdcol + 2) = ws1.cells(rowno + 1, sdcol + 2)                     'do nothing                 else                     ws2.cells(resultrow, sdcol + 2) = str(ws1.cells(rowno, sdcol + 2)) & "," & str(ws1.cells(rowno + 1, sdcol + 2))                 end if               'modify 'sign' cell in sheet("data2") if required                 if ws1.cells(rowno, sdcol + 3) = ws1.cells(rowno + 1, sdcol + 3)                     'do nothing                 else                     ws2.cells(resultrow, sdcol + 3) = ws1.cells(rowno, sdcol + 3) & "," & ws1.cells(rowno + 1, sdcol + 3)                 end if              resultrow = resultrow + 1          else             'copy data 'as is' sheet("data2")             ws1.range(cells(rowno, sdcol), cells(rowno, ldcol)).copy _             destination:=ws2.cells(resultrow, sdcol)              resultrow = resultrow + 1          end if      rowno = rowno + 1  loop  end sub