Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-27-2021, 02:45 AM
Marcia's Avatar
Marcia Marcia is offline Assigning numbers to every group in a set of data Windows 7 32bit Assigning numbers to every group in a set of data Office 2013
Expert
Assigning numbers to every group in a set of data
 
Join Date: May 2018
Location: Philippines
Posts: 364
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Assigning numbers to every group in a set of data

Hi. I have a list of names that need to be grouped and numbered. I have a problem with the formula that assigns the number in each group where the names are not listed in consecutive rows.
I have attached a sample sheet.


Thank you.
Attached Files
File Type: xlsx NameCoding.xlsx (26.5 KB, 6 views)
Reply With Quote
  #2  
Old 03-27-2021, 08:45 AM
Purfleet Purfleet is offline Assigning numbers to every group in a set of data Windows 10 Assigning numbers to every group in a set of data Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 263
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Match kind of works for the name code as it will return the first match

=MATCH([@Name2],[Name2],0)

And for the name try

=IF([@Name3]<>"N","",INDEX([Name],MATCH([@Name3]&[@GroupName],[Name3]&[GroupName],0)))

Added extra columns to sheet tNames (2)
Attached Files
File Type: xlsx NameCoding_Purfleet.xlsx (36.5 KB, 2 views)
Reply With Quote
  #3  
Old 03-27-2021, 04:29 PM
Marcia's Avatar
Marcia Marcia is offline Assigning numbers to every group in a set of data Windows 7 32bit Assigning numbers to every group in a set of data Office 2013
Expert
Assigning numbers to every group in a set of data
 
Join Date: May 2018
Location: Philippines
Posts: 364
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Purfleet.

The MATCH formula must return 1, 2, 3, 4, 5, 6, 7, 8...... It left out numbers 2,3, 6, 17 and 18.

The formula IN THE GroupNameX must populate all the rows with the chosen name for each group by Name3.
Reply With Quote
  #4  
Old 03-29-2021, 01:41 PM
p45cal p45cal is offline Assigning numbers to every group in a set of data Windows 10 Assigning numbers to every group in a set of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

You have the UNIQUE function available? In cell E2:
=MATCH([@Name2],UNIQUE([Name2]),0)
copied down.
Reply With Quote
  #5  
Old 03-29-2021, 02:12 PM
p45cal p45cal is offline Assigning numbers to every group in a set of data Windows 10 Assigning numbers to every group in a set of data Office 2019
Expert
 
Join Date: Apr 2014
Posts: 478
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

I think Purfleet's solution will work for you if you use his
=MATCH([@Name2],[Name2],0)
in E2 (copy down if it doesn't happen automatically).
Then refresh your qUnique table.
Leave your VLOOKUP as it is in column G.

The numbers don't have to be contiguous values for this.


Without the qUnique table, try in column G
=INDEX([Name],MATCH([@NameCode]&"N",[NameCode]&[Name3],0))
Reply With Quote
  #6  
Old 03-30-2021, 12:37 AM
Marcia's Avatar
Marcia Marcia is offline Assigning numbers to every group in a set of data Windows 7 32bit Assigning numbers to every group in a set of data Office 2013
Expert
Assigning numbers to every group in a set of data
 
Join Date: May 2018
Location: Philippines
Posts: 364
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you p45Cal. I adopted the Unique formula because it is not skipping some numbers.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning shortcut to Original mode in Tracking group Editor Word 3 05-26-2019 07:12 AM
Assigning numbers to every group in a set of data Assigning the Legend to Data Columns jelkin1 Excel 1 12-10-2015 06:12 AM
Linking/Assigning Excel data 2 shapes (Timeline functionality) mediaset23 Visio 0 07-31-2015 04:18 AM
Trying to autofilter 8-10 rows of numbers to sort in a group Jack-P-Winner Excel 0 11-25-2013 10:55 PM
Assigning a single task to a team or group djcozens Project 1 07-09-2012 05:44 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft