Count using Query or Vlookup or other method in Google Sheet

Sample Data: Sht1
Nme Srn
abc   def
cba   fed
ghi    jkl
abc   def
rst    uvw
cba   fed
abc   def

sht2
Nme Srn Count
abc   def        3
cba   fed        2
ghi    jkl           1
rst   uvw        1

Say, I wish to create an automatic way to generate the result shown in sht2 column Count.
counting must be done on Name and Surname columns matching up. I thought of  a nested vlookup but not sure if will work... Tx
shaunwinginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
Method 1: Use a Pivot Table
- Highlight the cell range in Table A on sheet 1
- Under the "Data" tab, select "Pivot Table"
- Under Rows, select "Add field". Select Name
- Again, under Rows, select "Add field". Select Srn
- Uncheck the "Show totals" options on both row fields
-Under Values, Select name then Summarize by CountA.

Method 2: formulas
- copy all the name and past them in a new cell
- Highlight that new range and delete duplicates
- Create a new column called "Srn". Use a vlookup like =vlookup(A13, $A$2:$B$8, 2, false)
- Create a new column called "Count" Use a countif like =countif(A2:B8, A13)

I recommend using the pivot table because you get extra features but both methods should work
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I would prefer to achieve this through a VBA code rather.

Please find the attached workbook with the code. Enable macros/contents after opening the file to test the code.

How will code work?

Each time you select Sheet2, the code will be triggered and display a count of name and surname combinations in col. C. So if you add the more data on Sheet1 and then activate the Sheet2, it will show you the updated count.
Count-Names.xlsm
0
shaunwinginAuthor Commented:
This all needs to be done in a Google Sheet - not Excel I'm afraid....
Would like column Count to automatically be kept up to date

Note the unique  keys are FNM and SNM together.
Can have 2 people with same Surname....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
0
shaunwinginAuthor Commented:
tx - this is the idea I had. Only thing is the unique name and surn data in sheet2 is already there. I need to assign the name and surnames to sheet1 and keep a check on how many I've assigned by refering back to sheet2. Can one do away with this code in fields below of sheet 2 and have it still work?

Name      
=iferror(INDEX(Sheet1!A$2:A$300,SMALL(Sheet1!$D$2:$D$300,ROWS(A$2:A2))),"")
Srn
=iferror(INDEX(Sheet1!B$2:B$300,SMALL(Sheet1!$D$2:$D$300,ROWS(B$2:B2))),"")

I'm intrigued by this code - can you please explain  briefly how it works?

PS Pls allow edit of your sheet - so I can test

Do the names and surnames need to be sorted in Sheet2 if do away with above code?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Does that mean, you don't want to auto populate the unique names and surnames on Sheet2 and only want to get count in col. C if you input a name and Surname in col. A and B on sheet2?

If so, I have removed those Index formulas from sheet2 but the formula in col. C to get the count is still there. So once you input a Name and Surname in Col. A and B on Sheet2, the count will be auto populated in corresponding row in col. C.

Also you are free to make any changes on the sheet as I have created this sheet for you.

Is this what you are trying to achieve?
0
shaunwinginAuthor Commented:
Afraid not - Sheet2 names and surname are static. changes are made on Sheet1 - names and surnames are added. Sheet2 must show the count against each name and surname. Names and Surname in Sheet2 are unique. They may be duplicated in Sheet 1.
I've sent a modify request.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Request accepted. Did you get the privilege to modify the sheet?

My question was how do you get the unique Names and Surnames on Sheet2?
Because the formulas I placed in Col. A and B on Sheet2 were extracting the Unique Names and Surnames from the Sheet1.

Right now I have deleted the formulas in Col. A and B from Sheet2, I can place them again if you need them. You may just type your notes on Sheet1 in the blank area if you have something to share with me.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shaunwinginAuthor Commented:
Tx - working great - as the names and surnames in sheet2 are entered manually and then assigned in sheet 1.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad it worked for you. :)
0
shaunwinginAuthor Commented:
Btw, can you make a sheet 3 that would tell me which names on Sheet 1 don't appear on Sheet 2? I think it will be basically the code you removed....
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Formula added. Please look at sheet3.
0
shaunwinginAuthor Commented:
Perfect tx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google Apps

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.