Help Tallying Names from Pages in a Workbook

fnbcholt

New Member
Joined
Dec 1, 2016
Messages
2
I have an excel workbook with 4 pages of names. Page one is the 7000 names along with rows for the 4 products I have. Each of the other 3 pages is names of customers using those products. I need to put an X in the product on page 1 if the customer uses it from the other pages. Can anyone help me?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
So is your Page 1 look like this:

CustName Product1 Product2 Product3 Product4

and you want an X in each product column if the customer is using it?

If so, why are there only 3 other pages for products, shouldn't there be 4? And what does "each of the other 3 pages" look like?
 
Upvote 0
fnbcholt,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
fnbcholt,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

I do have 4 products. I should have said that page 1 is the first product and has all of the names, then each of the other pages has whoever uses a corresponding product. We are trying to tally the other products so we can go in to our system and mark what products they use. I have our sample that we have been playing with saved to dropbox. https://www.dropbox.com/s/f03gq8h5a74b2io/example-1.xlsx?dl=0
Thank you!
 
Upvote 0
So for example, you could put in B9:

=--ISNUMBER(match($A9, 'Product 1'!$A:$A, 0))

would give you a 1 if the customer uses Product 1, and 0 if he does not. For the next three products, just change 'Product 1' to 'Product 2' etc.
 
Upvote 0
fnbcholt,

Thanks for the workbook/worksheets.

Here is a macro solution for you to consider that is based on the structure of your workbook/worksheets.

Sample worksheets before the macro:


Excel 2007
A
1
2Full Name
3Kristopher Dorrell
4Clare Faris
5Lynelle Sepeda
6Dinah Corkery
7Jessica Gusman
8Donnetta Spector
9Aron Merriweather
10Betsey Koll
11Dannielle Eury
12Rashad Marvin
13Elmo Cude
14Iesha Moreman
15Mikki Erler
16Vi Delker
17Despina Czech
18
Product 1



Excel 2007
A
1
2Full Name
3Kristopher Dorrell
4Jeraldine Ericksen
5Dee Moring
6Saul Mclachlan
7Katrina Delano
8Sharyn Eagle
9
Product 2



Excel 2007
A
1
2Full Name
3Kristopher Dorrell
4Shandra Bonneau
5Dee Moring
6Fiona Beckert
7Dinah Corkery
8Jessica Gusman
9Juliette Santangelo
10Katrina Delano
11Shona Napier
12
Product 3



Excel 2007
A
1Full Name
2Carmelia Gautier
3Clare Faris
4Dee Moring
5Lynelle Sepeda
6Rogelio Derosa
7Saul Mclachlan
8Mariam Eicher
9Betsey Koll
10Kory Kubik
11
Product 4



Excel 2007
ABCDE
4Company 1
5
6
7
8Full NameProduct 1Product 2Product 3Product 4
9Kristopher Dorrell
10Carmelia Gautier
11Jeraldine Ericksen
12Shandra Bonneau
13Clare Faris
14Lindsay Frantz
15Jeffery Lingerfelt
16Corinna Buono
17Dee Moring
18Joane Dino
19Carmina Redfield
20Ralph Goza
21Lynelle Sepeda
22Esteban Figaro
23Dorothea Langley
24Fiona Beckert
25Rogelio Derosa
26Angela Mcclintic
27Dinah Corkery
28Saul Mclachlan
29Sebrina Edmonson
30Jessica Gusman
31Donnetta Spector
32Mariam Eicher
33Aron Merriweather
34Juliette Santangelo
35Raymonde Griffiths
36Betsey Koll
37Glinda Pinson
38Katrina Delano
39Kory Kubik
40Casandra Hoover
41Dannielle Eury
42Fannie Brittingham
43Mia Schilke
44Shona Napier
45Vannesa Redford
46Carli Toth
47Rashad Marvin
48Elmo Cude
49Sharyn Eagle
50Lanell Glantz
51Iesha Moreman
52Sybil Murakami
53Judie Boaz
54Mikki Erler
55Cassandra Dilworth
56Scarlet Ghee
57Vi Delker
58Despina Czech
59
Main


And, after the macro:


Excel 2007
ABCDE
4Company 1
5
6
7
8Full NameProduct 1Product 2Product 3Product 4
9Kristopher DorrellXXX
10Carmelia GautierX
11Jeraldine EricksenX
12Shandra BonneauX
13Clare FarisXX
14Lindsay Frantz
15Jeffery Lingerfelt
16Corinna Buono
17Dee MoringXXX
18Joane Dino
19Carmina Redfield
20Ralph Goza
21Lynelle SepedaXX
22Esteban Figaro
23Dorothea Langley
24Fiona BeckertX
25Rogelio DerosaX
26Angela Mcclintic
27Dinah CorkeryXX
28Saul MclachlanXX
29Sebrina Edmonson
30Jessica GusmanXX
31Donnetta SpectorX
32Mariam EicherX
33Aron MerriweatherX
34Juliette SantangeloX
35Raymonde Griffiths
36Betsey KollXX
37Glinda Pinson
38Katrina DelanoXX
39Kory KubikX
40Casandra Hoover
41Dannielle EuryX
42Fannie Brittingham
43Mia Schilke
44Shona NapierX
45Vannesa Redford
46Carli Toth
47Rashad MarvinX
48Elmo CudeX
49Sharyn EagleX
50Lanell Glantz
51Iesha MoremanX
52Sybil Murakami
53Judie Boaz
54Mikki ErlerX
55Cassandra Dilworth
56Scarlet Ghee
57Vi DelkerX
58Despina CzechX
59
Main


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub fnbcholt()
' hiker95, 12/05/2016, ME978880
Dim wm As Worksheet, wp1 As Worksheet, wp2 As Worksheet, wp3 As Worksheet, wp4 As Worksheet
Dim c As Range, n As Range, p As Range
Application.ScreenUpdating = False
Set wm = Sheets("Main")
Set wp1 = Sheets("Product 1")
Set wp2 = Sheets("Product 2")
Set wp3 = Sheets("Product 3")
Set wp4 = Sheets("Product 4")
With wp1
  For Each c In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(8).Find("Product 1", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp2
  For Each c In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(8).Find("Product 2", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp3
  For Each c In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(8).Find("Product 3", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp4
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(8).Find("Product 4", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wm
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the fnbcholt macro.
 
Upvote 0
fnbcholt,

Here is an updated macro for you to consider, that uses the same code sections for each of the 4 Product # worksheets.

Worksheets Product 1, Product 2, Product 3, Product 4, can have the same, or, different starting row for title Full Name.



Excel 2007
ABCDE
8Full NameProduct 1Product 2Product 3Product 4
Main


Worksheet Main, can have the above title row in any row, except row 1.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub fnbcholt_V2()
' hiker95, 12/05/2016, ME978880
Dim wm As Worksheet, wp1 As Worksheet, wp2 As Worksheet, wp3 As Worksheet, wp4 As Worksheet
Dim c As Range, n As Range, p As Range, fn As Range
Application.ScreenUpdating = False
Set wm = Sheets("Main")
Set wp1 = Sheets("Product 1")
Set wp2 = Sheets("Product 2")
Set wp3 = Sheets("Product 3")
Set wp4 = Sheets("Product 4")
With wm
  Set fn = .Columns(1).Find("Full Name", LookAt:=xlWhole)
End With
With wp1
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty And c <> "Full Name" Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(fn.Row).Find("Product 1", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp2
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty And c <> "Full Name" Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(fn.Row).Find("Product 2", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp3
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty And c <> "Full Name" Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(fn.Row).Find("Product 3", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wp4
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not c = vbEmpty And c <> "Full Name" Then
      Set n = wm.Columns(1).Find(c.Value, LookAt:=xlWhole)
      If Not n Is Nothing Then
        Set p = wm.Rows(fn.Row).Find("Product 4", LookAt:=xlWhole)
        If Not p Is Nothing Then
          wm.Cells(n.Row, p.Column).Value = "X"
        End If
      End If
    End If
  Next c
End With
With wm
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the fnbcholt_V2 macro.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top