Concatenate Numerous rows based off cells value

[SlightlyClueless]

Hi All,

I have a fairly large worksheet (around 4200 rows) of site access data that needs to be compiled in a specific way. I will try to explain this as best I can below. In summary, I need help with a concatenate formula that will concatenate rows based off the value in column B.

Link to sample workbook

Column A, "Site ID", lists the unique site identifier.
Column B, "Access Order", this lists the progression of access requirement. This can range from 1-7. So for instance a site may have 3 steps for access. It would show as

Site ID Access Order
ABC123 1
ABC123 2
ABC123 3

Column C, "Access Type", This lists the type of access point they will face
Column D, "Code", This is the code or notes for that point of access
Column E, "Key", This identifies the key type if applicable
Column F, "Concate Access & Code", Concatenates column C & D
Column G, "Concat All", This Would concatenate the data for each point of access based off of how many point of access their are (Column B). So if the highest number listed for a site is 5, it would concatenate the all 5 points of access for that specific site.

Site ID	Access_Order	Access Type	Code	Key	Concat Access & Code	Concat All
222722	1	Compound and cabinets	1234		Access Order - 1 Compound and cabinets - Code: 1234
223301	1	Compound Gate	4567	Key Type - Combo	Access Order - 1 Key Type - Combo Compound Gate - Code: 4567
223301	2	Shelter Door	55-a	Key Type - Key	Access Order - 2 Key Type - Key Shelter Door - Code: 55-a	Access Order - 1 Key Type - Combo Compound Gate - Code: 4567 Access Order - 2 Key Type - Key Shelter Door - Code: 55-a
223302	1	Compound Gate	1234	Key Type - Combo	Access Order - 1 Key Type - Combo Compound Gate - Code: 1234
223302	2	Shelter Door	55-a	Keybox/Lockbox Location - Magnetic key box under HVAC unit Key Type - Key	Access Order - 2 Keybox/Lockbox Location - Magnetic key box under HVAC unit Key Type - Key Shelter Door - Code: 55-a	Access Order - 1 Key Type - Combo Compound Gate - Code: 1234 Access Order - 2 Keybox/Lockbox Location - Magnetic key box under HVAC unit Key Type - Key Shelter Door - Code: 55-a
223303	1	Compound Gate	1234	Key Type - Combo	Access Order - 1 Key Type - Combo Compound Gate - Code: 1234
223303	2	AC Meter Compound Gate	55a	Key Type - Key	Access Order - 2 Key Type - Key AC Meter Compound Gate - Code: 55a
223303	3	Shelter Door	55a	Keybox/Lockbox Location - Shelter key is located in a magnetic key box under one of the HVAC units. Key Type - Key	Access Order - 3 Keybox/Lockbox Location - Shelter key is located in a magnetic key box under one of the HVAC units. Key Type - Key Shelter Door - Code: 55a	Access Order - 1 Key Type - Combo Compound Gate - Code: 1234 Access Order - 2 Key Type - Key AC Meter Compound Gate - Code: 55a Access Order - 3 Keybox/Lockbox Location - Shelter key is located in a magnetic key box under one of the HVAC units. Key Type - Key Shelter Door - Code: 55a
223304	1	compound gate	1234	Key Type - combo	Access Order - 1 Key Type - combo compound gate - Code: 1234
223304	2	shelter door	55A	Key Type - key	Access Order - 2 Key Type - key shelter door - Code: 55A
223304	3	Vendor access	located under HVAC unit with Shelter Key	Key Type - Magnetic Lock box	Access Order - 3 Key Type - Magnetic Lock box Vendor access - Code: located under HVAC unit with Shelter Key	Access Order - 1 Key Type - combo compound gate - Code: 1234 Access Order - 2 Key Type - key shelter door - Code: 55A Access Order - 3 Key Type - Magnetic Lock box Vendor access - Code: located under HVAC unit with Shelter Key

 

[SlightlyClueless]

Cross posted to Concatenate cells based off highest numerical value

 

[hagia_sofia]

Hello, below is a cumbersome attempt for "Concat All" (to be insterted in G2 and dragged down):

=LET(
ID,$A$2:$A$12,
ORDER,$B$2:$B$12,
TYPE,$C$2:$C$12,
CODE,$D$2:$D$12,
KEY,$E$2:$E$12,
a,IF(MAXIFS(ORDER,ID,A2)=B2,B2,""),
b,SEQUENCE(ROWS(ID)),
c,"Access Order - "&CHOOSEROWS(ORDER,SORT(SEQUENCE(a,,XLOOKUP(A2&B2,ID&ORDER,b),-1))),
d,CHOOSEROWS(CODE,SORT(SEQUENCE(a,,XLOOKUP(A2&B2,ID&ORDER,b),-1))),
e,CHOOSEROWS(TYPE,SORT(SEQUENCE(a,,XLOOKUP(A2&B2,ID&ORDER,b),-1)))&" - Code: "&d,
f,CHOOSEROWS(KEY,SORT(SEQUENCE(a,,XLOOKUP(A2&B2,ID&ORDER,b),-1))),
g,IF(ISNUMBER(CHOOSEROWS(ORDER,SORT(SEQUENCE(a,,XLOOKUP(A2&B2,ID&ORDER,b),-1)))),""),
h,HSTACK(c,f,e,g),
i,TEXTJOIN(CHAR(10),FALSE,h),
IFERROR(TEXTBEFORE(i,CHAR(10),LEN(i)-LEN(SUBSTITUTE(i,CHAR(10),""))),""))

Not sure if it is meant seriously but you could test it...

 

[SlightlyClueless]

THANK YOU hagia_sofia!!!! That worked perfectly!

 

[hagia_sofia]

Thanks for the positive feedback!