Tagging Type based on Criteria in the file

[shan]

Hello,

Request your assistance for macro code which will tagged Type as "CAT3" or "CAT4" in the attached file.

Step 1

Insert a column after destination and named as "TYPE"

Step 2

Insert the type i.e. CAT3 or CAT4 based on below criteria

Criteria

By default "TYPE" for all Origin will be "CAT3" but if destination became as Origin and Origin became as destination then it is CAT4

Example is highlighted in above

e.g. My Origin is BOM and Destination is DEL … this is CAT3 … if now DEL is Origin and BOM is Destination then CAT4

Thanks n regards,
Shan

[p45cal]

Attachment 13950See attached, on second sheet (Sheet1 (2)), there's a button which runs a macro.
See comments in the code.
The macro:

Sub blah()
Set myTable = Range("A3").CurrentRegion  'needs the table to be surrounded by blank cells (or the edge of the sheet).
Set DestnHeader = myTable.Rows(1).Find(what:="Destination", Lookat:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
Intersect(DestnHeader.Offset(, 1).EntireColumn, myTable).Insert Shift:=xlToRight
DestnHeader.Offset(, 1).Value = "Type"
TopDataRow = myTable.Row + 1
With DestnHeader.Offset(1, 1).Resize(myTable.Rows.Count - 1)
  .FormulaR1C1 = "=IF(SUMPRODUCT((R[-1]C[-2]:R" & TopDataRow & "C[-2]=RC[-1])*(R[-1]C[-1]:R" & TopDataRow & "C[-1]=RC[-2]))>0,""CAT 4"",""CAT 3"")"
  .Value = .Value  '(removes formulae)
End With
'enable the following 2 lines only if you want to see which rows a Dest/Origin reversal was found on - only applies to your sample sheet:
DestnHeader.Offset(1, 11).FormulaArray = "=MAX(--(R[-1]C[-12]:R4C[-12]=RC[-11])*(R[-1]C[-11]:R4C[-11]=RC[-12])*ROW(R[-1]C[-12]:R4C[-12]))"
DestnHeader.Offset(1, 11).AutoFill Destination:=DestnHeader.Offset(1, 11).Resize(myTable.Rows.Count - 1)
End Sub

[shan]

Great Sir,

Its working as desired. Thank you So much ... have a nice day...!!!

[shan]

Sir,

I have tagged this thread as SOLVED ... what should I do now....


I have ran the macro on a big data and noticed that for certain Origin and Destination "CAT 4" is reflecting instead of "CAT 3".
But for all the Origin and Destination which needs to be tagged as "CAT 4" are correctly tagged.

Screenshot.jpg

[p45cal]

Impossible to tell from a screenshot (and filtered data too).
Have you tried enabling the last 2 lines of the macro on a copy of your sheet? It shows in each case which row the reverse match above is coming from.
Otherwise attach a file with more data showing where it's going wrong.
I suspect it's to do with the data being in groups but there were insufficient data in your first sample file to determine with certainty how they were grouped?

[shan]

Hello Sorry for delay ...
Sir I have attached the file and highlighted the rows where it is going wrong.

[p45cal]

In your most recent attachment you say that row 6 (of the spreadsheet, not the table) BOM|DEL is incorrectly typed CAT4, however, according to your criteria: "if destination became as Origin and Origin became as destination then it is CAT4" this is true for the line directly above it DEL|BOM.
So why is it incorrect?

[shan]

Hello Sir,

I think I was not able to clearly mentioned my requirement. Actually by default all Origin and Destination should be tagged as CAT3 only if there is a vice versa then it should be tagged as CAT4...

e.g.

BOM DEL CAT3
BOM DEL CAT3
BOM DEL CAT3
DEL BOM CAT4
BOM DEL CAT3
DEL SXR CAT3
SXR IXJ CAT3
IXJ DEL CAT3
SXR DEL CAT4
DEL IXJ CAT4
SXR DEL CAT3

[p45cal]

Hello Sir,

I think I was not able to clearly mentioned my requirement. Actually by default all Origin and Destination should be tagged as CAT3 only if there is a vice versa then it should be tagged as CAT4...

e.g.

BOM DEL CAT3
BOM DEL CAT3
BOM DEL CAT3
DEL BOM CAT4
BOM DEL CAT3
DEL SXR CAT3
SXR IXJ CAT3
IXJ DEL CAT3
SXR DEL CAT4
DEL IXJ CAT4
SXR DEL CAT3

Why is the red not CAT 4?

[shan]

Because BOM DEL is already tagged as CAT3 ..

[shan]

By mistake

BOM DEL CAT3
BOM DEL CAT3
BOM DEL CAT3
DEL BOM CAT4
BOM DEL CAT3
DEL SXR CAT3
SXR IXJ CAT3
IXJ DEL CAT3
SXR DEL CAT4
DEL IXJ CAT4
SXR DEL CAT3

Actually SXR DEL should be CAT 4

[p45cal]

Because BOM DEL is already tagged as CAT3 ..

Not a valid reason because "By default "TYPE" for all Origin will be "CAT3""

[shan]

Sorry Sir, I think I am not able to explain you properly. I will come with some examples... give me some time.
Thank you for your support.