pvtjoker77
New Member
- Joined
- Aug 31, 2015
- Messages
- 13
- Office Version
- 2016
- Platform
- Windows
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]51
[/TD]
[TD]HNL@internet.com
[/TD]
[TD]HNL
[/TD]
[TD][/TD]
[TD]HNL@internet.com
[/TD]
[/TR]
[TR]
[TD]52
[/TD]
[TD][/TD]
[TD]SJC
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]OGG@internet.com
[/TD]
[TD]OGG
[/TD]
[TD][/TD]
[TD]OGG@internet.com
[/TD]
[/TR]
[TR]
[TD]54
[/TD]
[TD]HNL@internet.com
[/TD]
[TD]HNL
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I get the above results using the following formulas in U51:U56 And it removes the duplicate but how can I combine the non duplicate results from U51:U54 to have them them to look like
HNL@internet.com;OGG@internet.com? I know I have to use CONCAT function somehow.
End result is I would like to get them combined in any cell since I use it in a VBA that gets the email addresses from that cell to send and email
[TABLE="width: 1000, align: left"]
<tbody>[TR]
[TD]U51 =R51
[/TD]
[/TR]
[TR]
[TD]U52 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U51, $R$51:$R$54), 0))}
[/TD]
[/TR]
[TR]
[TD]U53 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U52, $R$51:$R$54), 0))}
[/TD]
[/TR]
[TR]
[TD]U54 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U53, $R$51:$R$54), 0))}
[/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD]
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]51
[/TD]
[TD]HNL@internet.com
[/TD]
[TD]HNL
[/TD]
[TD][/TD]
[TD]HNL@internet.com
[/TD]
[/TR]
[TR]
[TD]52
[/TD]
[TD][/TD]
[TD]SJC
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]OGG@internet.com
[/TD]
[TD]OGG
[/TD]
[TD][/TD]
[TD]OGG@internet.com
[/TD]
[/TR]
[TR]
[TD]54
[/TD]
[TD]HNL@internet.com
[/TD]
[TD]HNL
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I get the above results using the following formulas in U51:U56 And it removes the duplicate but how can I combine the non duplicate results from U51:U54 to have them them to look like
HNL@internet.com;OGG@internet.com? I know I have to use CONCAT function somehow.
End result is I would like to get them combined in any cell since I use it in a VBA that gets the email addresses from that cell to send and email
[TABLE="width: 1000, align: left"]
<tbody>[TR]
[TD]U51 =R51
[/TD]
[/TR]
[TR]
[TD]U52 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U51, $R$51:$R$54), 0))}
[/TD]
[/TR]
[TR]
[TD]U53 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U52, $R$51:$R$54), 0))}
[/TD]
[/TR]
[TR]
[TD]U54 {=INDEX($R$51:$R$54, MATCH(0, COUNTIF($U$51:U53, $R$51:$R$54), 0))}
[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: