How to combine similar cells, while merging data in other cells

Hi! I'm new here. Actually found another solution to something I was trying to figure out on here while I was browsing through forums posts to see if there was a solution for my task, which was a nice bonus! Anyway, here's what I'm trying to do: Let's say I have a spreadsheet, with 2 columns. In column A, I have a list of software installed on computers. Let's call these software titles Alpha, Beta, and Charlie. Column B is a list of IP addresses. Let's say 10.1.1.1 - 10.1.1.4. The goal of the spreadsheet is to show the software titles in column A only once (so only 3 rows), and to have column B as a list of the IP addresses that have that software installed.

[removed the example I typed up since it didn't format correctly; please reference attachment]

I've attached a sample of what I'm trying to do. Probably easier to look at the attachment. I've been looking at a bunch of YouTube videos and various tutorials online, but I cannot seem to figure out how to do this. I am not sure if the Consolidate option does this, but all the tutorials I found lead to Consolidate being used for mathematical functions, and I'm looking for more of a list approach.

Thank you in-advance for any insight that can be provided.

This is doable, but requires the use of VBA to accomplish in a single step.

To use VBA, copy the code below, go to Excel, hit Alt+F11, go to Insert -> Module, and paste the code in the window that pops up. Close that window.

Now you have a new function called ConcatIf.

Its useage: ConcatIf(criteria range, criteria, return range, optional delimiter in quotes)

So in B14: =ConcatIf($A$2:$A$10,A14,$B$2:$B$10,CHAR(10))

Note, I used the delimiter Char(10) which is code for "the next line" which is what it'll input between cells it pulls together. To make sure it appears normal, you'll need to turn on Wrap Text for the cell.

See attached for clarification

Please Login or Register  to view this content.