summarising_data..!

[via135]

hi all..!

i am having product names in A1:A10 with
col header in A1 as "products".
B1:F5 having the vendor codes like 001, 002, 003, 004 & 005.

B2:F11 there are "x" marks for the products authorised for the vendors
and
"blank" for the products not authorised..!

what i want is to get the vendor-wise summary of
products authorised & products not authorised
in seperate sheets.

is it possible to get the result using some macro..?

any hlp.?

-via135

 

[Bernard Liengme]

Here is a subroutine that makes new sheets with names machting vendors and
add list of approved and unapprove
Assumens the data is in Shet1.
You will need to delete sheets with names matching vendors if you re-run the
subroutine

Sub tryme()
Dim vendors(1 To 5)
Dim Approved(1 To 5, 1 To 10)
Dim UnApproved(1 To 5, 1 To 11)
With Worksheets("sheet1")
For j = 1 To 5
vendors(j) = Cells(1, j + 1)
For k = 1 To 11
If Cells(k + 1, j + 1) = "x" Then
Approved(j, k) = Cells(k + 1, "A")
Else
UnApproved(j, k) = Cells(k + 1, "A")
End If
Next k
Next j
End With
For j = 1 To 5
Debug.Print vendors(j)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = vendors(j)
Cells(1, "A") = "Approved"
Cells(1, "B") = "Unapproved"
n = 2
m = 2
For k = 1 To 11
If Approved(j, k) <> "" Then
Cells(n, "A") = Approved(j, k)
n = n + 1
Else
Cells(m, "B") = UnApproved(j, k)
m = m + 1
End If
Next k
Next j
End Sub

best wishes

 

[via135]

Here is a subroutine that makes new sheets with names machting vendors and
add list of approved and unapprove
Assumens the data is in Shet1.
You will need to delete sheets with names matching vendors if you re-run the
subroutine

Sub tryme()
Dim vendors(1 To 5)
Dim Approved(1 To 5, 1 To 10)
Dim UnApproved(1 To 5, 1 To 11)
With Worksheets("sheet1")
  For j = 1 To 5
    vendors(j) = Cells(1, j + 1)
    For k = 1 To 11
      If Cells(k + 1, j + 1) = "x" Then
         Approved(j, k) = Cells(k + 1, "A")
       Else
         UnApproved(j, k) = Cells(k + 1, "A")
      End If
    Next k
 Next j
 End With
 For j = 1 To 5
  Debug.Print vendors(j)
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = vendors(j)
  Cells(1, "A") = "Approved"
  Cells(1, "B") = "Unapproved"
  n = 2
  m = 2
  For k = 1 To 11
    If Approved(j, k) <> "" Then
      Cells(n, "A") = Approved(j, k)
      n = n + 1
    Else
     Cells(m, "B") = UnApproved(j, k)
     m = m + 1
    End If
   Next k
 Next j
End Sub

best wishes

i am getting only one sheet inserted named with
the first vendor code ie) "1" with the lists of "approved" &
"unapproved"
in col A & col B respectively..and then an error message
"Runtime error 9 subscript out of range"

-via135

 

[via135]

i am getting only one sheet inserted named with
the first vendor code ie) "1" with the lists of "approved" &
"unapproved"
in col A & col B respectively..and then an error message
"Runtime error 9 subscript out of range"

-via135

what i want is that only 2 sheets
need to be inserted with names
"approved" & "unapproved"
with the vendors names in A1:E1
with the column-wise respective product lists
in each of the sheet separately.!

-via135