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

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

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"via135" wrote in message
...
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

On Apr 7, 2:27*am, "Bernard Liengme"
wrote:
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

--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"via135" wrote in message

...



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

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

On Apr 7, 7:57*am, via135 wrote:
On Apr 7, 2:27*am, "Bernard Liengme"
wrote:





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

--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"via135" wrote in message

....

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

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