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

	A	B	C	D	E	F	G	H
	OPTION A
1	Relationship Type	Company Name/Last name	Contact 1 - First Name	Contact 1 - Last Name	Contact 1 - Email	Contact 2 - First Name	Contact 2 - Last Name	Contact 2 - Email
2	Solicitor	The Law Firm	Fred	Smith	fred.smith@thelawfirm.com.au	John	Brown	john.brown@thelawfirm.com.au
3	Accountant	Bean Counter	Criag	Toms	craig.tome@beancounters.com
4	Real Estate	Hot Property	David	Jones	djones@hotproperty.com.au	Anne	Hayes	anne.hayes@hotproperty.com.au
	OPTION B
1	Relationship Type	Company Name/Last name	Contact 1 - First Name	Contact 1 - Last Name	Contact 1 - Email
2	Solicitor	The Law Firm	Fred	Smith	fred.smith@thelawfirm.com.au
3	Solicitor	The Law Firm	John	Brown	john.brown@thelawfirm.com.au
4	Accountant	Bean Counter	Criag	Toms	craig.tome@beancounters.com
5	Real Estate	Hot Property	David	Jones	djones@hotproperty.com.au
6	Real Estate	Hot Property	Anne	Hayes	anne.hayes@hotproperty.com.au

<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>
</tbody>

 

[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.

=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.

<tbody>
</tbody>

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