Microsoft Excel - Need experts solutions in 2 excel problems

Hi All,

can anyone help me to solve my 2 excel problems,

First one I have a pcking list which have 7 checklist(CL) like the example below

Packing CL1  CL2  CL3  CL4  CL5  CL6  CL7  Score

Now if all 7CL's are yes available then the score will be 100%, if 6CL's available the score will be 85.71% ((100/7)*6), if 5CL's available then the score will be 71.42% ((100/70*5) and like that.

Second problem, liking it with the first one I might have more than one packings in a shipment like example below

shipment 1  CL1 CL2 CL3 CL4 CL5 CL6 CL7 Score
Packing 1
Packing 2
Total Shipment 1

Shipment 2  CL1 CL2 CL3 CL4 CL5 CL6 CL7 Score
packing 1
packing 2
packing 3
Total Shipment 2

is there a formula like when once i finish with packing 1 the next below cell will let me choose packing 2 or total in order to decrease manual job, total will be the sum of yes's per CL's in order to have the average score.

That's all,

Thanks everyone in advanced for your help, and looking for the solutions soon :)

Hi filas said,

Just 1st approach in quick with formula to work arround :)    ...(I will come back with macro later)

• Format column A with custom format like this "Shipment "#
  • Then to input the shipment number, just input number 1, 2, 3 ect.
• Column B, input this data validation, list as "Packing " and "Total "
• C4 input this function =IF(ISNUMBER(A4),1,C2+1)
  • you will get the number for Packing
• D4 input this =IF(A4<>"",A4,D3)
  • you will get the number for shipment
• E4 input this =IF(B4="Packing ",B4&" "&C4,IF(B4="Total ","Total Shipment "&D4,""))
  • You will get packing no or total shipment no. depends on what you select in column B
• for the calculation the "Yes" use this formula =100%/7*COUNTIF(F4:L4,"Yes")

** of course you can hide the column C and D 

Hope this help

pichart y

Hi firas said,

here is the piece of code...

• For I cannot attach the file...I give you the code here
  • in module 1
    • Sub autoPacking()
      
      
      ShipmentRow = Range("A" & Rows.Count).End(xlUp).Row
      Shipment = Cells(ShipmentRow, "A").Value
      If ActiveCell.Value = "Packing " Then
      ActiveCell.Value = "Packing " & ActiveCell.Row - ShipmentRow + 1
      ElseIf ActiveCell.Value = "Total " Then
      ActiveCell.Value = "Total " & Shipment
      
      
      End If
      Cells(ActiveCell.Row, "J").FormulaR1C1 = "=100%/7*COUNTIF(RC[-7]:RC[-1],""Yes"")"
      Cells(ActiveCell.Row, "C").Select
      End Sub
  • in sheet that you work, put this event procedure
    
    • Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 2 Then Call autoPacking
      End Sub
• Hope this help

pichart Y.

Hi firas said,

Now I can attach the file...here it is ---> Vba_PackingMonitoring (version 2).zip

What you can do with this file...

• in column A input the shipment No. as usual
• click in column B you will find the list for you to chose, 
  • if you choose the packing, then instead of packing  there, you will find the No. of packing too
  • it will count packing and put the number..
  • At the same time, column J will be filled, the formula to cal % of yes
  • if you select the total, will be automatically "Total Shipment #" there for you.
• I still have question, what do you want to calculate in the row of total... let me know if you need any help.
• How dose this file work...
  • I create the module to do..all the process above using this code
    • Sub autoPacking()
      ShipmentRow = Range("A" & Rows.Count).End(xlUp).Row
      Shipment = Cells(ShipmentRow, "A").Value
      If ActiveCell.Value = "Packing " Then
      ActiveCell.Value = "Packing " & ActiveCell.Row - ShipmentRow + 1
      ElseIf ActiveCell.Value = "Total " Then
      ActiveCell.Value = "Total " & Shipment
      End If
      Cells(ActiveCell.Row, "J").FormulaR1C1 = "=100%/7*COUNTIF(RC[-7]:RC[-1],""Yes"")"
      Cells(ActiveCell.Row, "C").Select
      End Sub
  • Then use event procedure to run that module
    • Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 2 Then Call autoPacking
      End Sub

Hope you like this..

Pichart Y.

How are you indicating that a checklist is available?  Do you have a set of data containing checklists, of do you just want the 1-7 list with yes/no's?

With the 1-7 list, you can just use a countif:

=countif(A2:a8,"Yes")/7

formatted as a percentage.  With a dataset, which would probably be a better data model, you would need a more extensive formulas, but it would be more robust.

Same thing with your second consideration, you could use countifs with multiple criteria for shipments and packing lists.

Hi Pichart Y.

You did it man, you are the expert :), Thank you so much for your help. Highly apprechiate it.

Best regards,

Firas

Thanks Wally eye for your comment.

Best regards,

Firas