I have a fairly large worksheet (around 6800 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
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 Type | Concat Access | Concat All for site |
|---|---|---|---|---|---|---|
| 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 |
| 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 |
| 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 Access Order – 3 |
| 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 Access Order – 3 |
| 223305 | 1 | Compound Gate | 8722 | Key Type – Combo | Access Order – 1 Key Type – Combo Compound Gate – Code: 8722 |
|
| 223305 | 2 | Shelter Door | 5A-2 | Keybox/Lockbox Location – Shelter key is located in a magnetic key box under one of the HVAC units. Key Type – Key |
Access Order – 2 Keybox/Lockbox Location – Shelter key is located in a magnetic key box under one of the HVAC units. Key Type – Key Shelter Door – Code: 5A-2 |
Access Order – 1 Key Type – Combo Compound Gate – Code: 8722 Access Order – 2 |