Combining 8 columns into 5

[MarkBeau]

Hi

Option A is what I have on my screen which has 500 rows of data and not all rows have information in columns F G & H, option B is what I'm wishing to achieve. Is there a way of achieving this without VBA, if so would appreciate if you can advise. If VBA is the answer can someone assist. My VBA skills are limited plus I wish to understand what the scripting means so any comments on the scripting would be appreciated.

Thanks Mark


[TABLE="class: grid, width: 1299"]
<colgroup><col width="124" style="width:93pt"> <col width="174" style="width:131pt"> <col width="144" style="width:108pt" span="2"> <col width="213" style="width:160pt"> <col width="144" style="width:108pt"> <col width="141" style="width:106pt"> <col width="215" style="width:161pt"> </colgroup><tbody>[TR]
[TD="width: 124, align: center"][/TD]
[TD="width: 124, align: center"]A[/TD]
[TD="width: 174, align: center"]B[/TD]
[TD="width: 144, align: center"]C[/TD]
[TD="width: 144, align: center"]D[/TD]
[TD="width: 213, align: center"]E[/TD]
[TD="width: 144, align: center"]F[/TD]
[TD="width: 141, align: center"]G[/TD]
[TD="width: 215, align: center"]H[/TD]
[/TR]
[TR]
[TD="width: 124, align: center"][/TD]
[TD="width: 124"]OPTION A[/TD]
[TD="width: 174"][/TD]
[TD="width: 144"][/TD]
[TD="width: 144"][/TD]
[TD="width: 213"][/TD]
[TD="width: 144"][/TD]
[TD="width: 141"][/TD]
[TD="width: 215"][/TD]
[/TR]
[TR]
[TD="align: center"]1

[/TD]
[TD]Relationship Type[/TD]
[TD]Company Name/Last name[/TD]
[TD]Contact 1 - First Name[/TD]
[TD]Contact 1 - Last Name[/TD]
[TD]Contact 1 - Email[/TD]
[TD]Contact 2 - First Name[/TD]
[TD]Contact 2 - Last Name[/TD]
[TD]Contact 2 - Email[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Solicitor[/TD]
[TD]The Law Firm[/TD]
[TD]Fred[/TD]
[TD]Smith[/TD]
[TD]fred.smith@thelawfirm.com.au[/TD]
[TD]John[/TD]
[TD]Brown[/TD]
[TD]john.brown@thelawfirm.com.au[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Accountant[/TD]
[TD]Bean Counter[/TD]
[TD]Criag [/TD]
[TD]Toms[/TD]
[TD]craig.tome@beancounters.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Real Estate[/TD]
[TD]Hot Property[/TD]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]djones@hotproperty.com.au[/TD]
[TD]Anne[/TD]
[TD]Hayes[/TD]
[TD]anne.hayes@hotproperty.com.au[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD]OPTION B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Relationship Type[/TD]
[TD]Company Name/Last name[/TD]
[TD]Contact 1 - First Name[/TD]
[TD]Contact 1 - Last Name[/TD]
[TD]Contact 1 - Email[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Solicitor[/TD]
[TD]The Law Firm[/TD]
[TD]Fred[/TD]
[TD]Smith[/TD]
[TD]fred.smith@thelawfirm.com.au[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Solicitor[/TD]
[TD]The Law Firm[/TD]
[TD]John[/TD]
[TD]Brown[/TD]
[TD]john.brown@thelawfirm.com.au[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Accountant[/TD]
[TD]Bean Counter[/TD]
[TD]Criag [/TD]
[TD]Toms[/TD]
[TD]craig.tome@beancounters.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Real Estate[/TD]
[TD]Hot Property[/TD]
[TD]David[/TD]
[TD]Jones[/TD]
[TD]djones@hotproperty.com.au[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Real Estate[/TD]
[TD]Hot Property[/TD]
[TD]Anne[/TD]
[TD]Hayes[/TD]
[TD]anne.hayes@hotproperty.com.au[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. There will be lots of different ways but heres one:

Dim sh As Worksheet, lr As Long, a As Long, arr
Set sh = Sheets("Sheet1") 'sheet name with data
lr = sh.Range("A" & Rows.Count).End(xlUp).Row 'last row of data
a = 2 'first row to use to place new data
arr = sh.Range("A2:H" & lr) 'get data into array
For i = LBound(arr, 1) To UBound(arr, 1) 'loop through array
    sh.Range("J" & a) = arr(i, 1)
    sh.Range("K" & a) = arr(i, 2)
    sh.Range("L" & a) = arr(i, 3)
    sh.Range("M" & a) = arr(i, 4)
    sh.Range("N" & a) = arr(i, 5)
    If Len(arr(i, 6)) > 0 Then 'check if name 2 exists
        a = a + 1
        sh.Range("J" & a) = arr(i, 1)
        sh.Range("K" & a) = arr(i, 2)
        sh.Range("L" & a) = arr(i, 6)
        sh.Range("M" & a) = arr(i, 7)
        sh.Range("N" & a) = arr(i, 8)
    End If
    a = a + 1
Next

 

[Mike Szczesny]

Hi Mark,

Can you something like this? I will you a very simple set a data. Assume your first set of emails is in A1:A4 and your second set of emails is in C1:C4. Assume your new column title is in A7. Place your formula in A7. This formula works. You need to use Cntrl+Shift+Enter. [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$A$4,MATCH(0,COUNTIF($A$7:A7,$A$1:$A$4),0)),INDEX($C$1:$C$4,MATCH(0,COUNTIF($A$7:A7,$C$1:$C$4),0))) copy down.[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps.
Mike Szczesny

 

[MarkBeau]

Mike

At the end of the formula you have count down, as it is outside the formula does it represent something...Thanks Mark

=IFERROR(INDEX($A$1:$A$4,MATCH(0,COUNTIF($A$7:A7,$A$1:$A$4),0)),INDEX($C$1:$C$4,MATCH(0,COUNTIF($A$7:A7,$C$1:$C$4),0))) copy down.

 

[MarkBeau]

Steve the fish

Thanks for your help....Mark

 

[Mike Szczesny]

That just means copy this formula down to get all the items