+ Reply to Thread
Results 1 to 7 of 7

Conditional Concatenation of Multiple IP Address Values Into One Cell

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    Mascard, PH.
    MS-Off Ver
    Office 365
    Posts
    4

    Question Conditional Concatenation of Multiple IP Address Values Into One Cell

    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 `Get-WmiObject` 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 `VLOOKUP` 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 `Table B` that lists its name `PC1.abc.com` 3 times for its 3 IP addresses of `10.10.10.15`, `192.168.10.11`, and `172.16.25.23`, then that computer's entry (1 row) in `Table A` should have a value under the `IP Address` column which should be equal to the combination of all those 3 IP addresses, separated by commas (`,`) -- `10.10.10.15, 192.168.10.11, 172.16.25.23`. I was hoping to use purely non-macro-enabled Excel for this. Thanks

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    7,016

    Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    Upload a sample file with result expected.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    08-13-2018
    Location
    Mascard, PH.
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    Thanks for the reply.

    Please see attached sample.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,637

    Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    Hi jeesoon. Welcome to the forum.

    Since you have Office 365 the concatenation isn't a problem.

    Not seeing how you you determined the names in A16:A21 I just copied down from the first section.

    For the concatenated addresses array enter this in C16 and fill down. If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then this formula does not have to be committed CSE. Just Enter will do.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Registered User
    Join Date
    08-13-2018
    Location
    Mascard, PH.
    MS-Off Ver
    Office 365
    Posts
    4

    Lightbulb Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    Thank you very much, Sir!

    The correct TEXTJOIN formula worked wonders!
    It's just what was needed! :-)


    Thank you also for the INDEX formula:
    Please Login or Register  to view this content.
    So I now have another option for the code below, which is what I was using:
    Please Login or Register  to view this content.

    A million thanks!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,637

    Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    You are welcome. Thank you for the feedback and rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    08-13-2018
    Location
    Mascard, PH.
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Conditional Concatenation of Multiple IP Address Values Into One Cell

    Done. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 05-01-2015, 02:07 PM
  2. Complex concatenation of values from multiple combo boxes
    By swood15 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2014, 06:59 PM
  3. [SOLVED] Conditional String Concatenation based on different cell being empty or not
    By MrGadget6977 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-16-2013, 01:14 PM
  4. Automate concatenation between single cell and multiple target cells
    By garnerseo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-16-2013, 07:15 AM
  5. Concatenation of multiple columns relativly to current cell
    By lesoies in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:34 PM
  6. Replies: 1
    Last Post: 09-27-2012, 02:12 PM
  7. Need Concatenation Of Values Across Cell Range
    By sureshpillitla in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2012, 12:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1