Conditional Concatenation of Multiple IP Address Values Into One Cell

JeeSoon

New Member
Joined
Aug 13, 2018
Messages
10
I have the ff. tables in a worksheet:
Table A (Sheet 1) - a list of 1000+ computers that don't have a specific piece of software installed
Table B (Sheet 2) - a list of 15000+ host-to-IP address entries from the local DNS obtained using the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Get-WmiObject</code> PS cmdlet.
We're in the process of investigating why the number of computers generated for Table A remain high, and I need to get their IP addresses from Table B. I was able get the corresponding IP addresses of the machines in Table A from their corresponding DNS entries in Table B by using the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">VLOOKUP</code> function. The big hurdle that I have is for those hundreds of machines with two or more IP addresses recorded in DNS, as there many laptops that roam and get associated with different subnets. How would it be possible to concatenate the IP address values of two or more cells into one cell for each machine that has multiple IP addresses associated with it using a formula? Is there a way to make the formula intelligent enough to detect multiple-entry hostnames and perform the needed concatenation of their multiple IP addresses, and just get the single IP address values for those with just single-entry hostnames?
For example if a single computer has 3 entries (3 rows) in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Table B</code> that lists its name <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">PC1.abc.com</code>3 times for its 3 IP addresses of <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">10.10.10.15</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">192.168.10.11</code>, and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">172.16.25.23</code>, then that computer's entry (1 row) in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Table A</code> should have a value under the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">IP Address</code> column which should be equal to the combination of all those 3 IP addresses, separated by commas (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">,</code>) -- <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">10.10.10.15, 192.168.10.11, 172.16.25.23</code>. I was hoping to use purely non-macro-enabled Excel for this. Thanks :)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
If you have Excel 2016 then you could achieve this with TEXTJOIN(). If you have an earlier version then I think you'll have to use a macro. Example of TEXTJOIN like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">pc1.abc.com</td><td style=";">141.127.24.43</td><td style=";">Lookup:</td><td style=";">pc1.abc.com</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">pc2.abc.com</td><td style=";">198.163.222.122</td><td style=";">Values:</td><td style=";">141.127.24.43, 236.139.169.58, 145.15.249.84</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">pc3.abc.com</td><td style=";">210.39.4.164</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">pc4.abc.com</td><td style=";">243.224.238.98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">pc1.abc.com</td><td style=";">236.139.169.58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">pc2.abc.com</td><td style=";">44.234.55.130</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">pc3.abc.com</td><td style=";">228.149.9.72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">pc4.abc.com</td><td style=";">30.214.15.160</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">pc1.abc.com</td><td style=";">145.15.249.84</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">pc2.abc.com</td><td style=";">113.19.202.50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">pc3.abc.com</td><td style=";">76.178.191.203</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">pc4.abc.com</td><td style=";">211.157.174.40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=TEXTJOIN(<font color="Blue">", ",TRUE,IF(<font color="Red">$A$1:$A$12=$D$1,$B$1:$B$12,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

WBD
 

JeeSoon

New Member
Joined
Aug 13, 2018
Messages
10
Wow! It worked!!! Thank you so much for the TEXTJOIN formula! You nailed it, WBD. If there was only a way to add multiple Thanks and Likes. :):):):)
 

JeeSoon

New Member
Joined
Aug 13, 2018
Messages
10
Wow! It worked!!! Thank you so much for the TEXTJOIN formula! You nailed it, WBD. If there was only a way to add multiple Thanks and Likes. :):):):)

By the way, how can I mark this as SOLVED?
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
No problem; glad it was an easy solution. There's no such thing as "SOLVED" on the forum.

WBD
 

JeeSoon

New Member
Joined
Aug 13, 2018
Messages
10
Hi,

It's me again.
There's one thing I wasn't prepared for, and that is the situation wherein a match would not be true.

For instance, if the lookup value is `PC99.abc.com` and no such value is found in the lookup table (Table B), how can I output something like "no record found" instead of just a blank value ("")?
I tried using alpha, numeric, other values, and all cells ended up with the "#VALUE!" error even for cells that are supposed to have IP address(es).

Thanks again. :)
 

JeeSoon

New Member
Joined
Aug 13, 2018
Messages
10
Additional to quoted query below, what if I only need to match the first 15 chars of the hostname value?

So if the hostname is "THIS-IS-A-VERY-LONG-PC-NAME.abc.com", then only the THIS-IS-A-VERY part of the name needs to be matched.

Thanks again. :)


Hi,

It's me again.
There's one thing I wasn't prepared for, and that is the situation wherein a match would not be true.

For instance, if the lookup value is `PC99.abc.com` and no such value is found in the lookup table (Table B), how can I output something like "no record found" instead of just a blank value ("")?
I tried using alpha, numeric, other values, and all cells ended up with the "#VALUE!" error even for cells that are supposed to have IP address(es).

Thanks again. :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,804
Office Version
2010
Platform
Windows
For instance, if the lookup value is `PC99.abc.com` and no such value is found in the lookup table (Table B), how can I output something like "no record found" instead of just a blank value ("")?
I cannot test this as I am using XL2010, but this should work...

=IF(COUNTIF(A:A,D$1),TEXTJOIN(", ",TRUE,IF($A$1:$A$12=$D$1,$B$1:$B$12,"")),"No Record Found")

Still array-enter the formula using CTRL+SHIFT+ENTER as instructed in Message #2 .



Additional to quoted query below, what if I only need to match the first 15 chars of the hostname value?

So if the hostname is "THIS-IS-A-VERY-LONG-PC-NAME.abc.com", then only the THIS-IS-A-VERY part of the name needs to be matched.
Again, I cannot test this, but assuming the above formula works for you, then give this a try...

=IF(COUNTIF(A:A,D1&"*"),TEXTJOIN(", ",TRUE,IF(LEFT($A$1:$A$12,15)=LEFT($D$1,15),$B$1:$B$12,"")),"No Record Found")
 
Last edited:

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Thanks Rick; I missed the follow up.

I would agree here but with a small mod on the first part:

Code:
=IF(COUNTIF($A$1:$A$12,LEFT($D$1,15)&"*"),TEXTJOIN(", ",TRUE,IF(LEFT($A$1:$A$12,15)=LEFT($D$1,15),$B$1:$B$12,"")),"No Record Found")
Entered with Control+Shift+Enter to create an array formula.

WBD
 

Watch MrExcel Video

Forum statistics

Threads
1,095,235
Messages
5,443,277
Members
405,223
Latest member
Industrial_Eng_SA

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top