A Challenge... multiple lines of data to match and with conditions

bk1702 · Jul 18, 2012

Hi, I'm trying to match multiple cells of data to a table. I figured out (using other threads) how to get one set to match another, but am trying to figure out how to get multiple sets to match in different orders without spelling them each out. In the end I'm looking for the name that will match the various cuts. All of the cut data has to match but it doesn't matter which order the cuts are in. Thanks for any help!

This is my current formula in C14 (using CSE):

{=IF(D5="No Cut",INDEX($AE$3:$AE$759,MATCH(C4&C5&C6&C7&C8&C9&C10&C11&C12&C13,$AH$3:$AH$759&$AI$3:$AI$759&$AJ$3:$AJ$759&AK3:AK759&AL3:AL759&AM3:AM759&AN3:AN759&AO3:AO759&AP3:AP759&AQ3:AQ759,0)),IF(E5="No Cut",INDEX($AE$3:$AE$759,MATCH(D4&C5&C6&C7&C8&C9&C10&C11&C12&C13,$AH$3:$AH$759&$AI$3:$AI$759&$AJ$3:$AJ$759&AK3:AK759&AL3:AL759&AM3:AM759&AN3:AN759&AO3:AO759&AP3:AP759&AQ3:AQ759,0)),"three cuts"))}

[TABLE="class: grid, width: 553"]
<TBODY>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]CUT 1
[/TD]
[TD]CUT 2
[/TD]
[TD]CUT 3
[/TD]
[TD]CUT 4
[/TD]
[TD]CUT 5
[/TD]
[TD]CUT 6
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Type:
[/TD]
[TD]Shoulder
[/TD]
[TD]Shoulder
[/TD]
[TD]Lap
[/TD]
[TD]No Cut
[/TD]
[TD]No Cut
[/TD]
[TD]No Cut
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Cut Direction:
[/TD]
[TD]Web
[/TD]
[TD]Web
[/TD]
[TD]Web
[/TD]
[TD]Web
[/TD]
[TD]Web
[/TD]
[TD]Web
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]LocX:
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]LocY:
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Cut Depth Start:
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Cut Depth End:
[/TD]
[TD]MAX
[/TD]
[TD]MAX
[/TD]
[TD]MAX
[/TD]
[TD]MAX
[/TD]
[TD]MAX
[/TD]
[TD]MAX
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]A dimension:
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]24
[/TD]
[TD]24
[/TD]
[TD]24
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]B dimension:
[/TD]
[TD]63
[/TD]
[TD]63
[/TD]
[TD]45
[/TD]
[TD]45
[/TD]
[TD]45
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]C dimension:
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]End Cut:
[/TD]
[TD]three cuts
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Sample of Data Table:
[TABLE="class: grid, width: 1145"]
<TBODY>[TR]
[TD][/TD]
[TD]AE
[/TD]
[TD]AG
[/TD]
[TD]AH
[/TD]
[TD]AI
[/TD]
[TD]AJ
[/TD]
[TD]AK
[/TD]
[TD]AL
[/TD]
[TD]AM
[/TD]
[TD]AN
[/TD]
[TD]AO
[/TD]
[TD]AP
[/TD]
[TD]AQ
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name
[/TD]
[TD]Sequence of Cut (1.2 is 1of2, 2.2 is 2of2)
[/TD]
[TD]# of Cuts
[/TD]
[TD]Type
[/TD]
[TD]Direction of Cut
[/TD]
[TD]LocX
[/TD]
[TD]LocY
[/TD]
[TD]Cut Depth Start:
[/TD]
[TD]Cut Depth End:
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]EC1
[/TD]
[TD]1.1
[/TD]
[TD]Cut 1
[/TD]
[TD]Rectangle
[/TD]
[TD]Web
[/TD]
[TD]75
[/TD]
[TD]75
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]196
[/TD]
[TD]25
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]L64X64X6.4 COPE 27X7
[/TD]
[TD]1.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Rectangle
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]27
[/TD]
[TD]WH-FT
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]L64X64X6.4 COPE 27X7
[/TD]
[TD]2.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Lap
[/TD]
[TD]Flange
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]27
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Reverse Snipe 0Lx27deg w/9x9 Snipe
[/TD]
[TD]1.3
[/TD]
[TD]Cut 3
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]38.5653
[/TD]
[TD]23.45
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Reverse Snipe 0Lx27deg w/9x9 Snipe
[/TD]
[TD]2.3
[/TD]
[TD]Cut 3
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]TAN(88.0628)
[/TD]
[TD]WH
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Reverse Snipe 0Lx27deg w/9x9 Snipe
[/TD]
[TD]3.3
[/TD]
[TD]Cut 3
[/TD]
[TD]Radius
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]71
[/TD]
[TD]EC11B_MT125X6.7/FLG17DEG
[/TD]
[TD]1.4
[/TD]
[TD]Cut 4
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]72
[/TD]
[TD]EC11B_MT125X6.7/FLG17DEG
[/TD]
[TD]2.4
[/TD]
[TD]Cut 4
[/TD]
[TD]Snipe
[/TD]
[TD]Flange
[/TD]
[TD]24.004
[/TD]
[TD]MIN
[/TD]
[TD]121.767
[/TD]
[TD]MAX
[/TD]
[TD]19.437
[/TD]
[TD]65.502
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]73
[/TD]
[TD]EC11B_MT125X6.7/FLG17DEG
[/TD]
[TD]3.4
[/TD]
[TD]Cut 4
[/TD]
[TD]Radius
[/TD]
[TD]Web
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]19
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]74
[/TD]
[TD]EC11B_MT125X6.7/FLG17DEG
[/TD]
[TD]4.4
[/TD]
[TD]Cut 4
[/TD]
[TD]Rectangle
[/TD]
[TD]Flange
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]121.767
[/TD]
[TD]MAX
[/TD]
[TD]24.004
[/TD]
[TD]FW
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]722
[/TD]
[TD]L64X64X6.4 COPE 15X7
[/TD]
[TD]1.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Rectangle
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]15
[/TD]
[TD]WH-FT
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]723
[/TD]
[TD]L64X64X6.4 COPE 15X7
[/TD]
[TD]2.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Lap
[/TD]
[TD]Flange
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]15
[/TD]
[TD]6.4
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]734
[/TD]
[TD]Reverse Snipe 0Lx15deg
[/TD]
[TD]1.1
[/TD]
[TD]Cut 1
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]TAN(86.25)
[/TD]
[TD]WH
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]735
[/TD]
[TD]Reverse Snipe 0Lx4deg
[/TD]
[TD]1.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]TAN(76.96)
[/TD]
[TD]WH
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]736
[/TD]
[TD]Reverse Snipe 0Lx4deg
[/TD]
[TD]2.2
[/TD]
[TD]Cut 2
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]737
[/TD]
[TD]Reverse Snipe 0Lx60deg
[/TD]
[TD]1.1
[/TD]
[TD]Cut 1
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]23.0940
[/TD]
[TD]40
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]738
[/TD]
[TD]Reverse Snipe 0Lx9deg
[/TD]
[TD]1.1
[/TD]
[TD]Cut 1
[/TD]
[TD]Snipe
[/TD]
[TD]Web
[/TD]
[TD]0
[/TD]
[TD]MIN
[/TD]
[TD]MIN
[/TD]
[TD]MAX
[/TD]
[TD]TAN(83.66)
[/TD]
[TD]WH
[/TD]
[TD]0
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks,

bk1702 · Jul 19, 2012

So, I've now named each of the arrays and come up with this (I added another set of columns for two cuts so each one is in one row):

{=IF($D$6="No Cut",INDEX(OldName,MATCH(Cut_One,col1cut&col2ty&col3dir&col4lx&col5ly&col6cds&col7cde&col8A&col9B&col10C,0)),IF(E6="No Cut",INDEX(OldName,MATCH(Cut_Two&One,col1cut&col2ty&col3dir&col4lx&col5ly&col6cds&col7cde&col8A&col9B&col10C&col_20&col_21&col_22&col_23&col_24&col_25&col_26&col_27&col_28,0)),"three cuts"))} CSE formula

I sitll don't know how to do more than matching 2 rows, as i'd have to create each possible combination in all possible orders. There's got to be a way to do this...

bk1702 · Jul 19, 2012

Ultimately I'm looking for the Name (in C14) from column AE that matches the data C5:H12 with the data in columns AI to AQ.

Each column in C5:H12 represents a 'cut' on a piece of material. There can be multiple cuts on each piece of material.

For Example: cells AE4 and AE5 have the same name: L64X64X6.4 COPE 27X7 there are two different cuts on one item as indicated in column AH. So C5:C12 much match either AI4:AQ4 or AI5:AQ5 and D5:D12 must match the opposite row that C5:C12 matches, the order of each cut doesn't matter only that they match.

This is where my problem lies. I can do the items with one cut with the "&" but when you throw in more than one cut it no longer works. I was able to do 2 cuts by adding another set of columns to do the second cut, however with 3 (or more cuts) the number of rows must go up to account for the many possibilities. There are a max of 6 cuts per piece of material.

Please let me know if this is confusing...
Thanks!

bk1702 · Jul 19, 2012

Simplification of same basic issue:

http://www.mrexcel.com/forum/showthread.php?648080-Formula-Help-CSE-amp-Match-with-Multiple-Criteria

bk1702 · Jul 31, 2012

Anyone?

A Challenge... multiple lines of data to match and with conditions

bk1702

New Member

Excel Facts

bk1702

New Member

bk1702

New Member

bk1702

New Member

bk1702

New Member

Similar threads

Forum statistics

Share this page

A Challenge... multiple lines of data to match and with conditions

bk1702

New Member

Excel Facts

bk1702

New Member

bk1702

New Member

bk1702

New Member

bk1702

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock