# Formula That Creates Unique Number For Repeating List Numbers?

Jun 26, 2013I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.

Project Work Id

8069000

8069000

8069000

8069000.1

[Code]....

I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.

Project Work Id

8069000

8069000

8069000

8069000.1

[Code]....

ADVERTISEMENT

I have a formula-generated defined list of names. I need to select them in random order without duplication and without choosing any blanks in the list.

View 6 Replies View RelatedI need to make a list that has 90 people and each day I need it to pick 4 random people. I need it to keep rotating people daily for the entirety of the monthfrom the first day of the month without utilizing the same people over again. I not excel savy at all so I need it dummy proof

View 1 Replies View Relatedi have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?

View 9 Replies View RelatedSay column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.

View 5 Replies View Relatedi'm currently trying to count the number of unique numbers in a Column using this formula:

=SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))

So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*"

=SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))

On this one I get #VALUE! and i can't figure out why.

I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.

I have a column where I have record numbers. The record number can end up duplicate in some cells.

PROBLEM_ID

IBM-02204732

IBM-03252238

IBM-03252238

IBM-03335648

IBM-03534918

IBM-03534918

IBM-03862015

IBM-03862015

IBM-12737629

IBM-12737629

IBM-17269762

I would like the get a formula that can count the number of unique numbers in that column?

I would like to get if possible to get a formula that summarize the number into a single cell.

I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.

I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.

However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....

Here is a sample of data that I have:

code name lastname

TS19SylvainBrook

TS19SylvainBrook

TV04ValérieMusoni

TV04ValérieMusoni

VB05BenValiquette

VB05BenValiquette

VB05BenValiquette

VI02IsabelleVanasse

VR07RichardVivo

VR07RichardVivo

VR07RichardVivo

WD01DanyWilliams

WD01DanyWilliams

WD01DanyWilliams

How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee)

In this case, the desired result would be 6. (the count function does NOT give the desired result..)

In case i creates a list using validation in column d ( for ex list contains either A or B or C ), now i want to create another lis in column e but wants that this new list will relate to list of colum D , i.e. in case some one selects A from drop sown list of column D then one list appear containing some options to select or if he selects b from drop down list in column D then diff list appear in column e ,

View 9 Replies View RelatedI've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.

I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.

But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.

I have a database (ATTACHED) which contain name, roll number, courses taken etc. I want to create different file for different courses showing the roll number and name of the student who have written their name as REGISTER in the course.

View 3 Replies View Relatedi need to create an user form that creates an ordered list when an input for the starting number, the ending number, and the increment is entered by the user. the ordered list must also start in the specified cell that is entered by the user. i understand how to design the user form but the coding necessary for making the macro baffles me.

View 4 Replies View RelatedI need to count the unique values in a list based on another value. i.e.

AB ___ Jones

AB ___ Smith

BC ___ Henry

AB ___ Jones

BC ___ Brown

BC ___ Henry

AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

formula that turns doubles into unique numbers example:

7788873 = 783

each number is in a cell each....

I need to count how many unique ID numbers there are per month.I have figured out how to count the unique ID's for the first month (Jan) but after that I'm having trouble trying to get the count right for Feb and Mar. Formula I used in cell E2 is: {=SUM(IF($A$2:$A$14

View 7 Replies View RelatedI'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.

Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?

Sample Workbook: datavalhelp.xls

I would like a formula that will give me the unique list of items in Col A but will give them to me in alphabetical order. Sample list of data and desired result below.

List of ItemsDesired Result

Lamp Chair

Table Desk

Chair Filing Cabinet

Filing CabinetLamp

Lamp Phone

Desk Table

Phone

Chair

B20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.

If i change the sheet name below, B21:F21 would also automatically change.

I have a column that looks something like (it is VERY long and has over 20 different names):

abe

abe

abe

abe

ben

ben

ben

cat

cat

john

john

john

john

tex

tex

I want to create another column (automatically) that lists each of of those once:

abe

ben

cat

john

tex

Any idea of how I can do this automatically?

product1

product1

product2

product1

product2

product4

product1

product2

product1

product4

Need the simple formula to list only unique records of above data

unique list is

product1

product2

product3

I have a list of names in column A these names repeat themselves a lot and I don't know how many there are.

now I want to make a list of the names in column c starting at C2 that looks down row A finds names and puts them in column C one after the other.

I have an excel assignment that has about 4000 orders for wood. I am suposed to find the company that ordered the most wood and of what kind. I am really at a loss for how to do this and have combed over my book quite afew times trying to figure it out. However it has little if any excel info and nor does the ** that came with it outside of how to install data analysis tools. I figure I need to find a way to get excel to combine the numbers together for orders from the same company but after trying for afew hours I have gotten no where.

View 3 Replies View RelatedI have a spreadsheet that lists numbers in a column. They increase 1 number at a time, however the same number is listed 24 times before increasing to the next. Scaled down to 3 times it would look like this: 111, 222, 333, 444, 555, each one of those individual numbers would have their own cell. When I highlight the column and drag down, it starts doing decimals..how do I get it to recognize it? sample.xlsx

I want to drag it down to continue the pattern. Right now, I type a couple of numbers and drag to finish the section.

In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 without repeating the same numbers.

At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

=INT(RAND()*(80-1)+1)

How can I change the formula or replace it?

A

B

c

d

[Code]....

So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3

I have repeating 7 names in A column.(Like a name group)

Each group names repeating after a blank(empty) row.

I want to write to B column for each repeating names from 1 to 7 as number value.

I have three cells in a column. A1, A2 and A3. There are only 4 possible numbers that these can have in them, 0,1,2,and 3. Here is the catch. None of them can repeat, although they can be in any order. And the column may only have data in the first cell or the first two or all three. Is there a simple macro that will alert me to when a number is repeated? It needs to be a macro rather than a apreadsheet function.

View 9 Replies View RelatedI am trying to do something that is probably impossible, but I figured if anyone could come up with a solution, it would be you folks here at Mr. Excel, so here goes.

I'm trying to set up two columns in Excel 2000; for simplicity sake, let’s just call them Column A and Column B. Each row in Column A will contain a code (a combination of letters and numbers) that represents one of 65 different workstations, and they could repeat multiple times. Each row in Column B will contain a number that represents a specific tool, and there are potentially hundreds of different tool numbers.

What I am trying to accomplish is to have something alert me when any tool number in column B appears alongside more than one workstation code in column A. It would really be nice if something like conditional formatting could be used to change the text to red or something when this happened. I know I can use filtering to accomplish this, but with hundreds of different tool codes, filtering just takes too long.

I am busy with a very large dataset +-20 000 records showing the movement data

and I am having problem with inserting missing data: I have columns titled day hour and position, but multiple records for each as data was initally recorded by second. Data looks like this

Day Hour Position

1 2 Z5

1 2 Z5

1 3 Z5

1 3 Z4

1 16 Z1

12 2 Z3

13 0 Z3

What I need to end up with is a sequential dataset running from day 1 to 388 with hours 1-24 of each day. Positions need to be filled in for all data recorded (see above). The big problem is that movements between positions need to be recorded and accounted for i.e if more than >80 of time was spent at the position Z3 then that is the position for the hour.

Copyrights 2005-15 www.BigResource.com, All rights reserved