Conditional Concatenation of Multiple IP Address Values Into One Cell

[JeeSoon]

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

 

[wideboydixon]

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:

Book1
	A	B	C	D
1	pc1.abc.com	141.127.24.43	Lookup:	pc1.abc.com
2	pc2.abc.com	198.163.222.122	Values:	141.127.24.43, 236.139.169.58, 145.15.249.84
3	pc3.abc.com	210.39.4.164
4	pc4.abc.com	243.224.238.98
5	pc1.abc.com	236.139.169.58
6	pc2.abc.com	44.234.55.130
7	pc3.abc.com	228.149.9.72
8	pc4.abc.com	30.214.15.160
9	pc1.abc.com	145.15.249.84
10	pc2.abc.com	113.19.202.50
11	pc3.abc.com	76.178.191.203
12	pc4.abc.com	211.157.174.40
Sheet1

Cell Formulas
Range	Formula
D2	{=TEXTJOIN(", ",TRUE,IF($A$1:$A$12=$D$1,$B$1:$B$12,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[JeeSoon]

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]

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]

No problem; glad it was an easy solution. There's no such thing as "SOLVED" on the forum.

WBD

 

[JeeSoon]

Ah OK. Anyway, thanks again. Till next post.

 

[JeeSoon]

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]

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]

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")

 

[wideboydixon]

Thanks Rick; I missed the follow up.

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

=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