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

 

[Rick Rothstein]

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.

Yeah, I was too fast with that (the wife had an errand for me to run and I wanted to get that out quickly before I left)... thanks for catching it.

 

[JeeSoon]

Thank you very much, Rick! Tried 'em both, and both worked on the sample table when used on the Excel of O365. =)

 

[JeeSoon]

Thanks a lot for this again, WBD! The fomula worked on the test table in Excel O365!
Always good to see the different possible ways of coming up with the desired results. =)

 

[JeeSoon]

Thank you very much again, guys!

Just to give feedback on something which I'm sure both of you know very well...
I'm not sure if it had anything to do with how I translated the formula to work with my worksheet, but as far as how things went, here are the results:
- Tried both versions on a single worksheet using a replica of the sample table in this thread -- both were able to capture the 15-character matches and output the "no record found" where needed.
- Used the formula on my actual worksheet where the lookup table (DNS Table) is on a separate sheet from the table that needs the queries for the matching IP addresses -- both formulas outputted the "no record found" on non-matches, but the modded version captured the first-15-character matches.

Really appreciate all you guys for helping out here at Mr. Excel. Thanks for not tiring out!
Thank you very much again, WBD and Rick!

=)

 

[JeeSoon]

​

Thank you very much again, guys!

Just to give feedback on something which I'm sure both of you know very well...
I'm not sure if it had anything to do with how I translated the formula to work with my worksheet, but as far as how things went, here are the results:
- Tried both versions on a single worksheet using a replica of the sample table in this thread -- both were able to capture the 15-character matches and output the "no record found" where needed.
- Used the formula on my actual worksheet where the lookup table (DNS Table) is on a separate sheet from the table that needs the queries for the matching IP addresses -- both formulas outputted the "no record found" on non-matches, but the modded version captured the first-15-character matches.

Really appreciate all you guys for helping out here at Mr. Excel. Thanks for not tiring out!
Thank you very much again, WBD and Rick!

=)

OK, I just found out where I was doing it wrong... here's a correction to my previous post (quoted above, and particularly in blue)...

The reason why both worked is because I was actually looking up a value with the 15-character-only version of the name (without ".abc.com").
Looking up the 15-character computername + ".abc.com" value actually ends up with "no record found".

So, just as was raised and then acknowledged, the added mod was needed.
Thanks again for all the contributions. =)