Column B of a worksheet (“Original Export”) contains a product type, column D contains a subtype, and E contains a list of attributes for the products:

ATTR-MaxPressure: 120 psig, ATTR-MaxTemp: 180F / 82C, ATTR-Media: Air,
ATTR-Media: Inert Gas, ATTR-Pneu_Actuation: Push Button,
ATTR-Pneu_Collection: Directional Valve, ATTR-Pneu_Connection: Lead
Wire, ATTR-Pneu_Material: Brass, ATTR-Pneu_Porting: Threaded Port,
ATTR-Pneu_ThreadSize: 10-32 UNF, ATTR2-VOLTAGE INPUT:Manual,
image-needed, Pneumatic, PNEUMATIC-VALVES, TYPE:VALVE

The attributes vary by product Type and SubType. Each Type has a separate worksheet. On the Type worksheet, column B has the Subtype, and columns C through Q contain the expected attributes.

Here’s a portion of the Pneumatic sheet:

Here’s another portion of same sheet, where instead of just the attribute names we also have potential values. We only want to search for the attribute name before the colon. BUT we want to return the entire attribute name and value. So basically need to search for an attribute like “ATTR1-ThreadSize:”, and take a substring starting there through the next comma or end of the string.

I’m creating a new “Summary Worksheet” with each attribute occupying its own column. I have the Type in column B and the subtype in column D of the new worksheet.

Starting with column F, I want to populate the attributes and values:

=IF(F$1>$E4,””,IFERROR(MID(‘Original Export’!$E2, FIND(IFERROR(INDEX(INDIRECT(“‘” & $B4 &
“‘!”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)&”:”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)),
MATCH(TRIM($D4), INDIRECT(“‘” & $B4 & “‘!$B:$B”), 0)), “”), ‘Original
Export’!$E2), FIND(“,”, ‘Original Export’!$E2,
FIND(IFERROR(INDEX(INDIRECT(“‘” & $B4 &
“‘!”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)&”:”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)),MATCH($D4,
INDIRECT(“‘” & $B4 & “‘!$B:$B”), 0)), “”), ‘Original Export’!$E2)) –
FIND(IFERROR(INDEX(INDIRECT(“‘” & $B4 &
“‘!”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)&”:”&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,COLUMN(C1),1)),
MATCH(TRIM($D4), INDIRECT(“‘” & $B4 & “‘!$B:$B”), 0)), “”), ‘Original
Export’!$E2)), “”))

This is working in some cases. However, in others it’s either not showing up or repeating the same attribute.

For reference, here is the Compressor description:

ATTR1-FLOW:3-5 CFM, ATTR2-PRESSURE:51-100 PSI, ATTR3-VACUUM:26+ “hg,
ATTR4-THREAD SIZE:1/4” NPT, ATTR4-VOLTAGE INPUT:120-240/50/60 AC,
pfs:label-free shipping, PNEUMATIC-COMPRESSOR, TYPE:COMPRESSOR

And here’s the first Valve description:

ATTR-MaxPressure: 120 psig, ATTR-MaxTemp: 180F / 82C, ATTR-Media: Air,
ATTR-Media: Inert Gas, ATTR-Pneu_Actuation: Push Button,
ATTR-Pneu_Collection: Directional Valve, ATTR-Pneu_Connection: Lead
Wire, ATTR-Pneu_Material: Brass, ATTR-Pneu_Porting: Threaded Port,
ATTR-Pneu_ThreadSize: 10-32 UNF, ATTR2-VOLTAGE INPUT:Manual,
image-needed, Pneumatic, PNEUMATIC-VALVES, TYPE:VALVE