PDA

View Full Version : Tagging Type based on Criteria in the file



shan
07-20-2015, 02:09 AM
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
07-20-2015, 09:27 AM
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
07-20-2015, 09:26 PM
Great Sir,

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

shan
07-20-2015, 10:02 PM
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.

13954

p45cal
07-21-2015, 12:28 AM
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
08-18-2015, 12:24 AM
Hello Sorry for delay ...
Sir I have attached the file and highlighted the rows where it is going wrong.

p45cal
08-18-2015, 07:40 AM
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
08-19-2015, 08:13 PM
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
08-20-2015, 06:16 AM
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 CAT3Why is the red not CAT 4?

shan
08-20-2015, 07:45 PM
Because BOM DEL is already tagged as CAT3 ..

shan
08-20-2015, 08:02 PM
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
08-21-2015, 11:56 AM
Because BOM DEL is already tagged as CAT3 ..Not a valid reason because "By default "TYPE" for all Origin will be "CAT3""

shan
08-22-2015, 04:50 AM
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.