Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I have a formula question. I've been trying to figure it out for some time, i'm hoping someone smarter than me can point me in the right direction. i've tried using if statments, match, count, max and so on but nothing is working.
I have a list of names, some of the names are duplicates. I would like to assign a number to the names. Next week the names will be different.
week one Week two
joe 1 sally 1
jeff 2 sally 1
larry 3 mary 2
jeff 2 lori 3
pete 4 monica 4
pete 4 mary 2This file is used to import data into our accounting software and each person needs an identifing number.
If someone could point me in the right direction i would greatly appriciate it.
I have a list of names, some of the names are duplicates. I would like to assign a number to the names. Next week the names will be different.
the example above needs more seperation between the names.
the list of names will not have numbers when they are entered into excel. i would like excel to assign an ordinal number to the list of names.
0 
I am sorry but I still do not understand.
You want, for instance, Mary is listed 17 times and that is more times then anyone else in the list of names. You want Mary to be ranked at the highest level or should I say the highest number in rank such as #1.
Re: assigning a number to the names but not having it in Excel???
In:
week one Week two
joe 1 sally 1
is Joe, 1, Sally, 1 in 4 columns or two? Is this how you find it or is this is what you want. Does the ranking care which week the name is in?The hard way:
Place all data in a single column.
Use the Advanced filter to copy Unique data to another column.
On the resulting data use COUNTIF and drag it down the list.Then use Sort Data Decending on the number column with the names in Ascending order.
Of course, if Joe has the same Rank as Sally then Joe will be listed first.
Now, Insert a column to the left of the sorted list of unique names and create your numbered list.
Then use VLOOKUP on the original data to find the identifier number per each name in an adjacent column.
Now use CONCATENATE to add the number to the names.
You now have the names with a ranking numeric value as part of the name.
The above process can take place in a relatively short period of time to complete.
Once you have what you need then do it again recording a macro to speed up the process for the next week. Naturally, you will need to adjust the macro to allow for changing sizes of lists.
I think I would need to see what you are seeing to provide an efficient manner to deal with the challenge. There are just too many unknown or unanswered questions to comprehend the situation (from here).
Regards,
Bryan
0
sorry, i'm having a hard time explaining what im trying to do.
i have one column. it contains names. some of the names are duplicates.
next to the names i have another column that contains the letters "api".
i would like to assign a number to the names and add it to the "api". (="api"&number)
if there are 4 names i need 4 numbers. preferably 1-4.
ie:
Ted
ted
jerry
larry
marythey do not have to be numbered in any order they just need numbers 1-4. both ted's could be api2, jerry api1, mary api3, larry api4.
the workbook is part of an import procedure; using filters, sorting and adding extra columns for calculation is not an option. th number needs to be generated without any intervention from the user.
0
Without formulas or filters or some sort of calculation then I know of no way to manipulate or modify data in a workbook.
In going beyond my abilities, can you tell me how the data gets in the workbook in the first place and what interaction takes place between the user and the workbook?
Bryan
0
Its a long, drawn out way of doing it, but why not rearrange the names column in alphabetical order? So:
A B C
1 joe 1
2 jeff =IF(A2=A1,1,0) =IF(B2=1,C1,C1+1)
3 larry
4 jeff
5 pete
6 peteYou end up with thre columns.. Do nothing in B1, Because you can assume its the first time Joe has appeared there. In C1, manually assign him 1 as his number.
Enter the two formulae above in B2 and C2, and drag them down the length of the page.. Your result looks like this:
Jeff 1
Jeff 1 1
Joe 0 2
Larry 0 3
Pete 0 4
Pete 1 4You can hide column B if it offends you! Hope this helps, like I said, very rough, longwinded idea.
DeeDM
0
Dee,
I am still following this thread but am at a loss as to how data can be manipulated if formulas or filters can't be used in the workbook. This is what gbrew584 requires.
Regards,
Bryan
0
Thanks for your input DeeDM. apparently i haven't explained what i am looking for. I will try again, thank you both for being so patient.
i have a workbook. it has 2 worksheets. one worksheet is external data. the extenal data is one column and is list of names which is generated from our accounting software.
the other worksheet is for the user.
the user, using the user worksheet, inputs data into colum A via the drop down box and data validation, the list is the data from the data worksheet.
once a name is selected i need to give that name a number. this number should appear in colum b. it is not an id number it is more like a bath or session number.
the formula in column b is ="api"&text(now(),"ddyy")&text(match(a1,a1:a254,0))
i have used the match formula to generate the number but this formula keeps skipping numbers. i do not want it to skip numbers.
if someone selects or inters joe in colum a row one, column b row one should show api01051.
if some selects mary in row three colum a she would be would be api01052 in row 3 column b.
every time the user selects mary she should be api01052. mary can be selected any number of times.
next week the user worksheet colum a is cleared and we start all over again.
when we start over mary may be api08056. or she may be the first name entered. the names are not selected or entered in any particular order. they are selected at random. joe could end up being api080519.
each week the number for mary will be differnt because, it is a new week and she may be entered in a different order.
here is another twist to add to the mix. every other row is equal to the row above it.
row one is the name selected from the dropdown box. row 2 is equal to row 1
row three is a name selected from the dropdown list, and row 4 is equal to 3. an so on. this applies to both colum a and b.in colum a row 2 =A1
in colum a row 4 =a3
in colum b row 2 =b1
in colum b row 4 =b3the names can not be sorted, filtered or rearranged, and we can not add columns we can only use two columns. the accounting software is picky.
once the user has selected a name the names are then imported back into the exeternal accounting file.
once the file is imported into the accounting software it combines all the api0519 (joe's) into one file. we cannot combine them before the file is imported.
0
Some time ago, I saw an e-mail requesting how to highlight an active cell in Excel 2003. I hope this will help.
one way to highlight current cell is using following macro in Excel 2003. It highlights the cell in yellow and if the baground is yellow, it choses a different color.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Note: Don't use if you have conditional
'// formatting that you want to keep'// On error resume in case user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex'// Leave On Error ON for Row offset errors
If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If'// Need this test in case Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1
Cells.FormatConditions.Delete'// Active cell color
With ActiveCell
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End WithEnd Sub
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
