Add Or Insert Number (1:50) To Row With No Duplication?
Nov 11, 2011
I've a list (Taken from this website; [URL] of 4900 rows with each row have 4 numbers if I want to add or insert one number( from 1 to 50 ) to each row with no duplicate to make it 5 numbers in each row? Is there a formula or Vb code to do this?
Code:
123411256212783129104121135512123161213147121543812162191217471012181911122023
12122230131224501412253315122641161227491712284218122932191234462012364421
1237392212384523124048241359251368261374427131013281311272913123630
[Code]...
To my calculation Numbers (1:50) have to be use 98 times;(50*98=4900) since each numbers in the list used 392 times(392/ 49 = 8)since there is no duplication when adding numbers (1:50) to rows each numbers must pair with evry other number in that list 8 times.at the moment with out adding the 5th number each number pairs with any other number exactly 24 times but adding process finish it should be 32 (24+8).
View 9 Replies
ADVERTISEMENT
Jun 8, 2009
each month i have to clens a sheet that shows customer name and then the figures per month.
this is pulled from a system that allows for a new entry each time a variation on customer name is used, I.E
Joe Blogs LTD
Joe Blogs Limited
Joe Blogs ltd.
would all come through as new entries. then i have to manually consolidate the figures from each month. the spreadsheet has over 5000 rows, and maybe only 15% are duplicated. what i want is to be able to select the column (that will be sorted a-z) and highlight cells that have a match for the first 4 or 5 charicters.
once i have identified duplicates i then have to merge the data into one row.
is there an easy way to do this? it is monthly, comparing 2008 and 2009, so by december there will be 24 columns + the company name that need to be clensed.
View 3 Replies
View Related
Jan 13, 2010
I have an excel sheet with about 500 words in 500 cells that I have generated automatically. Separately I have a 'blacklist' of words I have compiled manually that I don't want to appear in the excel list.
How do I ensure that any 'blacklisted' word is deleted from the excel files. Is there any other way than doing it manually?
View 5 Replies
View Related
May 5, 2008
i am having data in ColA & ColB in both Sheet1 & Sheet2
1) some records are both in Sheet1 & Sheet2
2) in Sheet1 itself some records are duplicated
3) in Sheet2 itself some records are duplicated
what i want is to extract the all the records in Sheet1 & Sheet2 in Sheet3
without duplication of records..!
View 10 Replies
View Related
Oct 4, 2012
how to setup formula for not allowed duplication in the column
View 1 Replies
View Related
Apr 27, 2014
A table re-organisation issue I have. Best described with an example I think:
Header
ID1
ID2
Q1
Q2
Q3
Q4
Data
1
8
A1
A2
A3
A4
[code]....
For the original data row (at the top) there are always 2 'ID' columns and 4 data columns.
There are about 100 rows, so the resulting table will be roughly 400 lines long.
I'm on the verge of doing this manually as the formulas and scrappy VB have so far been unsuccessful.
View 9 Replies
View Related
Jan 16, 2007
I made a macro that will name the current sheet whatever text is found in cell B2 on that sheet. This works great unless that sheet name already exists.
If the sheet name already exists, is there a way to rename it as Cell B2 plus the number 2 after the title so it doesn't cause an error?
Example:
"Smith, Bob" is a sheet name, so the macro would make the new sheet name "Smith, Bob 2"
Alternatively, how could I give a pop-up telling about the error and deleting the current sheet instead of renaming it with a "2"?
Eample:
"Sorry, 'Smith, Bob' already exists. Deleting new sheet." as a popup and have it delete the current sheet.
View 9 Replies
View Related
Aug 7, 2007
I have a spreadsheet that grows rapidly each day of addresses I service. Duplicates are not welcome. When the spreadsheet was smaller, I would type in an address and if it was a duplicate, Excel would auto fill the cell with the duplicate data. Now that the rows are over 2000, I wanted to see if this primitive method for weeding out duplicates was still working, however when I type the first address I have in cell A1, it does not auto fill in A2001, even though it is a duplicate.
Is there any way to have on-the-spot detection of duplicates sans macro?
View 9 Replies
View Related
Dec 8, 2008
I need formula in column F for count of duplication as mentioned in below table
for example
You can see Zone B IDs has 3 time duplication
Sheet1
ABCDEF1IDZone ZoneCount of Duplication21001B-I B-I331003B-I B-II241114B-II B-III251112B-II 62020B-III 71001B-I 82021B-III 91003B-I 101114B-II 112020B-III 121003B-I 132021B-III 141001B-I 151112B-II
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 24, 2014
I have two sheets, which has data in columns A-J (10) on both sheets and but (sheet2) will have more data records than (Sheet1). I would like to be able to run a vba macro, which can find duplicate rows between the two sheets, by matching up only 8 columns, excluding column's E and G between the two sheets and showing the output duplicates in both sheets in green highlight.
I have attached my sample data for further reference. [URL] .....
The sample file, shows data in sheet1 and sheet2, and the desired output should be sheet1 and sheet2, highlighting (2,3,4,5,6,7,9,19,11,13) rows green in both sheets.
View 14 Replies
View Related
Dec 7, 2008
I'm using excel 2007 and windows vista. I have 2 cells, A1 contains (01,) and B1 contains (01, 08). I want the result in cell C1 to be (01, 08) by calling function trim_sort($A1&$B1). The code gives me an error "invalid qualifier" for s.length & more.
View 4 Replies
View Related
Aug 9, 2012
in finding duplication within different worksheet tables using excel VBA.
Sheet 1 has the following table below:
A
B
C
D
E
F
G
[Code]..
The format of both tables is different within both of the sheets.
The desired output should be shown in sheet 2 table:
A
B
C
D
E
F
G
[Code]..
The above table (sheet 2) shows duplicate rows (bold) from comparing table in sheet 1.
I was using this function below, however this does not give me the desired output i am looking for.
=IF(AND($F2=Sheet1!$F2,$H2=Sheet1!$H2,$E2=Sheet1!$E2,$G2=Sheet1!$G2),Sheet1!I1,"").
View 7 Replies
View Related
Jan 9, 2014
I have a worksheet which J3:NJ3 lists the dates for the calendar year. (Example - J3 = 1/1 , K3 = 1/2 , etc. )
A3:C3 are my headers for project information (project name, manager,etc)
In D3 I will be entering employee names. in E3 I will be entering Project start dates, and in H3 will be end dates. These dates are selected via validation lists of the dates listed in J3:NJ3, and it is set to auto fill the adjacent date range with an "X" after the start and end dates have been selected.
Now I also have another worksheet which is a pivot table that will auto update upon any workbook change, and will show each employee and all of the dates in which they are scheduled to work. I am using conditional formatting on this sheet to highlight any dates that the selected employee is double booked on two or more projects.
I want to keep this second worksheet as is, but to avoid having to flip flop back and forth between tabs to find out of someone is double booked, I would like to add a column (I) which will either say "Available" or "Double Booked" . Is there a formula that I can use for this? I thought about Vlookup, but I don't believe I know of a way to write a Vlookup formula that can check a dynamic range of entered dates. I thought perhaps a nested Vlookup/Hlookup, but not quite sure if that will work either.
View 7 Replies
View Related
Feb 16, 2014
I have a time sheet in which i need user to enter a time sheet number in cell c14
I need a formula for that cell so the user can input the time sheet number but only be able to do this if the time sheet number they are imputing is the next number in sequence of the last one used.
View 1 Replies
View Related
May 2, 2007
I need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.
View 5 Replies
View Related
May 9, 2012
How do I compare 2 columns and then flag where there is duplication? I want to compare Column B against column C and then flag everywhere there is a match. In this case, Column B may have several thousands of entries (inclusive off all entries found in Column C) and I want to have Excel look through the thousands of entries in Column B and then just place an "x" where there was a match to the values found In Column C. For example:
Excel 2003ABC1Duplicate?MarchApril27899421458347889122254x21458112575445576332147x122258x11257921463Sheet1
View 9 Replies
View Related
Jan 29, 2013
Problem,
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 5000 1000 2000 12000
17/7/2012 12000 2000 2000 1000 11000
18/7/2012 11000 0 1000 5000 5000
18/7/2012 5000 10000 3000 0 12000
Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
Results :
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 7000 3000 3000 11000
18/7/2012 11000 10000 4000 5000 12000
(B=F)
if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
View 2 Replies
View Related
Oct 12, 2011
I have three colums on a spreadsheet the have a formula that calculates by taking the amount dividing it by 365 days then multiplying that by say 35 days. The day portion of the formula is the same in all cell in the three columns the number is the one this that changes by cell.
Is it possible to have a pop up box allowing the user to enter a number that will then be inserted into the formula. Instead of hitting F2 and manually entering the number.
View 3 Replies
View Related
Oct 8, 2008
I have a column that I want to insert number 1 through ? incrementing by one. Can I do this simply without having to type in each one manually?
View 4 Replies
View Related
May 21, 2009
somehow i managed to do this at first and then forgot to save my personal work book and cant remember how to do it.
What it is, is if cell A1 has a certain phrase in it ie Customer Reference then i want to insert four columns before it so it ends up in cell E1.
The other thing which i never managed to do is. I have 5 workbooks. master, book 1-4. I want to past all info in columns A:T from Book 1-4 into Master in the next available row.
View 10 Replies
View Related
Jul 30, 2013
Is there a way to insert a specified number of rows. For example, I want to insert 500 blank rows today but maybe tomorrow it will be 650 blank rows.
View 1 Replies
View Related
Sep 10, 2006
I've created a spreadsheet that tracks problems using an assigned problem number. Attached is the spreadsheet.
I need assistance in inserting a new row and incrementing the sequential number after pressing the "Get/Assign New NCR Number" button. I am successful at getting a new row added, but am not able to increment the number. In addition, I would also like to have the table locked so that no deleting or amending the sequential number is allowed.
View 9 Replies
View Related
Dec 19, 2011
We recently bought some new software at work, and I'm having trouble formatting the exported data in a way that will work with existing reports. The attached file is what the data looks like after being exported. Basically, what I'm trying to do is insert rows for the missing numbers -- i.e., each name should be followed by rows numbered 1 through 10.
John Smith John Smith
1 1
4 2
8 3
4
5
6
7
8
9
10
My ultimate end goal is to have columns of data for each name, instead of rows:
John Smith 1 2 3 4 5 6 7 8 9 10
I've created a formula that can do that part, but it operates on the assumption that each name will be followed by a set number of rows.
What little I know about vba I've learned on my own, so I may just be missing something obvious.
View 4 Replies
View Related
Jan 21, 2007
I've created an Invoice Template in excel. Each time I open it I'd like a certian cell "D3" to increase by one number starting at 2000. Each time this is opened a new customer information is input and then saved to their file. Please give me the very basicis on how to do this. I've already gone into other forums which have provided a code, but I have had any luck getting ti too work.
View 9 Replies
View Related
Dec 19, 2008
I want to insert call when they have double number, See attached sample.
View 6 Replies
View Related
Oct 13, 2009
I have a cell (B4)that may contain several different text values, i.e. Bill, Tom, John, Mike, Larry, & Dan. The value in cell D4 needs to relate to what text is in B4, i.e Bill = 6; Tom = 12; John = 8; Mike = 20; Larry = 15; & Dan = 10. I was trying an IF statement, but having trouble.
View 2 Replies
View Related
May 14, 2012
So I am trying to write a bit of code that will insert a number in sequence in column A if there is data in column B. I need the column to be numbered 1-### if column B contains any data. I not been able to find this code anywhere, just delete if or count if statements that do not give me what I need. I think I have a few pieces of the code (e.g. Active Cell Offset) but can not seem to fit together in the correct order.
View 3 Replies
View Related
Jan 21, 2007
I've created an Invoice Template in excel. Each time I open it I'd like a certian cell "D3" to increase by one number starting at 2000. Each time this is opened a new customer information is input and then saved to their file. Please give me the very basicis on how to do this. I've already gone into other forums which have provided a code, but I have had any luck getting ti too work.
View 9 Replies
View Related
Feb 7, 2008
I found lots of threads for inserting rows but not the one I need. I'm trying to insert rows with a macro on the position as specified in a cell f.e. in cell D4 I type the number 80 then the macro has to insert three rows at row 80
View 2 Replies
View Related
Mar 17, 2014
I have the following datas as an example;
A1: %90
A2: %100
A3: %75
According to those datas I want to get the following results;
B1: On going
B2: Done
B3: On going
So basically I want to tag columns which are equal to 100% as "Done", and the rest is as " On going".
View 2 Replies
View Related