VBA to Concat multiple columns based on multiple criteria

[OscarBlack]

Hi, this is my first time so I apologize for mistakes.

I have already looked all over internet and have not manage to find an answer to fit my question.

I have this set of data on sheet1

And I want to concatenate the IDs, Targets and DNS based on Name and Risk on Sheet2 like this
So if it has the same name eg: "Generic IWP" and different risks to categorize it differently
But if it has the same name eg: "Traceroute" and same risks to concat it as it shows.

 

[OscarBlack]

Hi again, is there something with my threat and no one helped me?

 

[OscarBlack]

HELP me please!

 

[Peter_SSs]

HELP me please!

I would think that at least one of the reasons it has taken so long for somebody to reply is that helpers have no easy sample data to test with, since we cannot copy/paste from your images into our Excel.
For the future, I suggest that you investigate XL2BB

See if this helps (in your 2019 version).

OscarBlack.xlsm
	A	B	C					H	I
1	ID	Target	DNS					Name	Risk
2	1	1.1.1.1	N/A					a	Info
3	2	1.1.1.2	example.com					b	High
4	3	1.1.1.2	N/A					a	Info
Sheet1

OscarBlack.xlsm
	B	C	D			G
1	Risk	Name	IDs			Targets
2	High	b	2			"example.com" (IP:1.1.1.2)
3	Info	a	1, 3			"N/A" (IP:1.1.1.1), "N/A" (IP:1.1.1.2)
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=TEXTJOIN(", ",1,IF(Sheet1!H$2:H$10=C2,IF(Sheet1!I$2:I$10=B2,Sheet1!A$2:A$10,""),""))
G2:G3	G2	=TEXTJOIN(", ",1,IF(Sheet1!H$2:H$10=C2,IF(Sheet1!I$2:I$10=B2,""""&Sheet1!C$2:C$10&""" (IP:"&Sheet1!B$2:B$10&")",""),""))

 

[OscarBlack]

Hello and thank you for your reply. Unfortunately I could not, due to strict regulations of security and privacy. Adding an untrusted plugin breaks company policy.
I have tried your formulas which unfortunately dont work. I was looking something on vba as the data is a lot and it would take formulas ages to load.
Thank you for your time and trying to help me.

 

[Peter_SSs]

Unfortunately I could not, due to strict regulations of security and privacy. Adding an untrusted plugin breaks company policy.

In that case you could consider copy/paste small sections from your worksheet directly into your post and identify the rows/columns like below. At least something we can copy.

Row/Col	B	C	D
1​	Risk	Name	IDs
2​	High	b	2
3​	Info	a	1, 3

I have tried your formulas which unfortunately dont work.

As you can see from my sheet it worked for me (at least as I understand the question). In what way did it not work for you?
- Error value?
- Wrong answer?
- Right answer in wrong place?
- Excel crashed?
- Something else?

 

[OscarBlack]

No Error or any prompt. I put the formulas exactly as it is and press calculate but nothing happens.
I have tested them on new excel with the exact names and data but no error or indication.
I am on excel 2019, double checked just to be sure.

I am attaching another example below based on your input that hopefully can give a better inside of what i am trying to achieve.

On Sheet1 I have this data:

A	B	C	H	I
ID	IP	HOST	NAME	RISK
1	1.1.1.1	example.com	RED	Info
2	1.1.1.1	example.com	BLUE	Info
3	1.1.1.1	example.com	BLUE	Info
4	1.1.1.2	example2.com	RED	Critical
5	1.1.1.3	example3.com	RED	High
6	1.1.1.4	example4.com	RED	Medium

And i want to produce this on Sheet2

B	C	D	E	G
RISK	NAME	ID	NAME & ID	IP & HOST
Info	BLUE	2,3	BLUE (ID: 2, 3)	"example.com" (IP: 1.1.1.1),"example.com" (IP: 1.1.1.1)
Critical	RED	4	RED (ID: 4)	"example2.com" (IP: 1.1.1.2)
High	RED	5	RED (ID: 5)	"example3.com" (IP: 1.1.1.3)
Medium	RED	6	RED (ID: 6)	"example4.com" (IP: 1.1.1.4)
Info	RED	1	RED (ID: 1)	"example.com" (IP: 1.1.1.1)

Compares and list all unique names with the Risk associated and concat the name with ID of column E and IP and host on column G
For example ID 2 and 3 on Sheet 1 have the same name and same risk so it will add them together but IDs 1,4,5,6 have same name but different risk so it will list them separately.
To clarify Sheet 2 is completely empty, there is no data all data is on Sheet1.

 

[Peter_SSs]

To clarify Sheet 2 is completely empty,

That was not clear at the beginning (to me at least). Your question was ..

And I want to concatenate the IDs, Targets and DNS based on Name and Risk

To me, that sounded like Name and Risk were available and you wanted help only with the concatenation of ID, Target & DNS.

If you are wanting to produce everything on Sheet2 then I think that you are going to need a vba approach. I'm afraid that I don't have sufficient time to look at that approach at the moment.

 

[OscarBlack]

At least thank you for asking and trying. Have a good day sir.