Macro to organise data, i have provided an example below, macro should only pick up unique codes and ignore any duplications.
From thisTo thisHiport IDIM IDHiport IDIM IDIM IDBHPBHP999AUDBHPBHP999AUDAUDBHPNNNNTBHPBHP999AUDTLSTLSWWWWPCCCTLS0000BHPAUDBHPNNNNTTLSTLSWWWWPTLSCCCTLS0000TLSCCCTLS0000
I have a database where each subject has data for 4 days and a number of different codes each day. I am looking to calculate how many different codes they have on day one, how many unique codes they have on each subsequent day and the sum of all these codes.
For example, in the data attached:
Subject 10002 = 2,1,0,0 (3)
Subject 10005 = 3,1,0,0 (4)
Subject 10009 = 3,1,1,0 (5)
A
B
C
D
E
F
1
ID
Code
Total IDs with Code 60
Total IDs with Code 345
Total IDs with Both Codes
[Code] .......
In column “A”, there are 14,000 ID Numbers. In column “B”, there is either a 60 or a 345 code beside the adjacent ID number in column “A”. There are duplicate IDs, some with same code and some that have both the 60 and 345 codes. I need to count ID numbers with code 60, code 345, and the IDs with both codes without duplication.
I have to type a an ID in column D of a worksheet called restricted funds, I have 13 sheets in that workbook and each sheet has in column D some IDs, I can not type a duplicate ID, in the that sheet. What is the best way to avoid no duplications. IDs are simply numbers.
On Worksheet Data 1 Column I there is a column of data with "some" duplicated text entries.
The data is sorted so that all of the duplications appear in a blocks, i.e.
This is the first
This is the first
This is the first
This is the second
This is the second
This is the third
This is the third
This is the third
This is the third
This is the fourth
This is the fifth
I want to -
Starting in Worksheet Data1 cell I4
Copy A4 to Worksheet Data2 cell A4
Then go back to Worksheet Data1 cell I4 drop down to cell I5 and if the data is the same as the data just copied drop down a cell and keep going until it reaches a cell with different non duplicated data.
Then Copy that cell to Worksheet Data2 cell A5
So that I end up with a list of data with no duplications.
I have a spreadsheet with 4 columns.
I would like to Compare Column A with Column B, if Column A contains text or numbers anywhere in Column B, then remove the corresponding rows not just in Column B, but Column C and D as well for that row.
I have a master sheet that has 3500ish names on it, and another sheet that I'll need to drop in a list of about 1000 names. What I need on the master is a way to check the dropped in data, find duplicated names, and flag them up.
Now, I believe what I did last time was have a true/false method of telling me if they're on both sheets, then use an IF formula to instead make Trues into "yes" and Falses into "no". However, I can't for the life of me figure out how I had it previously checking both sheets and confirming/denying if they're on both sheets or only appear once.
I have two workbooks (source and target) I want to copy column A in the source workbook to column A in the target workbook and remove duplication in the target workbook. If any.
[Code] .....
Found this in another forum it works for the copy but not the removal of duplication.
What is the line I need to add ?
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
We have a company and need to automating workflow.
The Master sheet contains incoming mail details by customer. Each employee is assigned a set number of customers to respond to. We want the information on the Master sheet to filter to a specific employee assigned sheet. On the employee assigned sheet, once filtered, they are to provide updates in column E
Date
Reference no.
Name
Employee ID
16-Feb-12
S/S/1
[Code] ........
We need a macro that can filter customers' details to the specific employee assigned sheet based on the employee ID in the master sheet. For example, all customers assigned to DW (i.e with the 'DW' employee ID) filters into a sheet called 'DW'. Please note that the Master sheet is a continuous log updated daily. This macro must not duplicate information previously filtered once the employee enters a status update in column E.
I have in column D starting D9, I have numbers starting at 1, and may finish at 100. But there could be duplicates, 1,2,3,3,4,5,6,6,7,7,8,9,10,10,11,12,13,.........
I would like to only copy the range D9 to H (End of column D), ignoring all the duplicate numbers, to another sheet.
So on the second sheet, it would be 1,2,3,4,5,6...... with the data copied from E,F,G and H.
I have the next situation:.......
Qmin - is the first smallest value than Q (in this case Qmin=[B5]=0.63)
Qmax - is the first highest value than Q (in this case Qmax=[B6]=0.77)
Hmin - the value attached to Qmin (in this case Hmin=[A5]=0.004)
Hmax - the value attached to Qmax (in this case Hmax=[A6]=0.005)
question: how can i make Qmin,Qmax,Hmin,Hmax to pick up themselfs if I have a given Q=[B12] value????
I have a worksheet that has all the numbers that I picked for the pick 5 lottery, listed from B24:N71. These are 5 different numbers. What I want to do is highlight the cells that equal the 5 numbers that were drawn, which I have located at S5,T5,U5,V5,W5
View 11 Replies View RelatedI am managing a group of sales reps and when they sell a product they receive commissions I am trying to develop something in excel that in cell B5 there will be a drop down that they can choose one of three products (call them A,B,C) then in C5 another drop down will be there w/ two options (new, refurb), then the result will be in D5 depending on the selections. The parameters would be:
Product A new = $10
B new = $20
C new = $30
A refurb = $5
b refurb = $15
C refurb = $25
So if the rep dropped down in cell B5 Product B then dropped down in cell C5 Refurb, the result would appear in D5 and would equal $15.
Basically I want a button that once pressed generate a message box that says a persons name. There are 3 names, so the message box should show name 1 1st and then when pressed again show name 2 and you guessed it then name 3!
View 9 Replies View RelatedAt the moment I have a problem as per example attached, that I need to select values from row and populate respective column based on the following.
1. A top row contains non-negative values
2. Rows below it contain blanks, negative values and positive value.
3. Last column to be populated with a value from the top row if the value in the rows below top row contains the last negative value before the first positive value. There could be blanks between the last negative and the first positive, but I want the last negative to be used as a criteria for selection of corresponding value from top row. I hope my question is clear with the attached example. The column to be populated is FY.
In short in the attached which is just 5 results (the full workbook has a couple of data tables and if/or's etc., I've stripped all of the workings out for ease) from one of the sims in the workbook, it shows how far each of the 8 teams listed will get.
Basically in the top table (results which I have highlighted green), I am just trying to return the values in column J (highlighted yellow) which I have just inputted in the attached. Basically I then intend to use them as a results cell for a data table (for each team). As you can see from looking at the 5 sets of results in the case of St.Louis Blues it would return, STANLEY, DIVISIONAL, SEMI, WIN, WIN.
I wasn't sure quite how to do this. I thought I could possibly do this with an index and a match (but wasn't quite sure how to express the function).
Results_Test.xlsx
In sheet1 columnA I have some data from that i want to copy randomly 50 rows to sheet2.
View 14 Replies View RelatedIs there a formula or function that allows me to always select the lowest (not min) actual lowest in a column? For example, I have a formula in A1 and I need it part of it to always reference the bottom of a certain range (i.e. A5.A200).
So one day the lowest would be A7 but a week later it could be A8. I don't want to have to keep changing the formula in A1 to find the lowest value. I will be adding data on a regular basis.
If I have a date that is 12 February 2008 I need to pick up data next to 31 January 2008 as that is nearest, however if my date is >15th February 2008 I want to pick up the data in the column next to 29 February 2008......
View 2 Replies View RelatedI am trying to create a front-page, control-panel style user form that among other things would enable the user to recolor certain aspects of the entire workbook based upon an eye-dropper tool that would pick any color from anywhere on screen (inside or outside of excel).
Ideally, I would like it to be a simple button, click on color, and then have a square next to the selection panel that would display this color. So an add-in would be the most viable solution, however, any form of coding that works is good enough for me.
I am currently at the stage of being able to enter a hex value into the form manually, display the color box using that hex-color in the form next to the selection panel, and upon completion of the form, edit which colors I need within the entire workbook.
And I have an external application that will select the hex value of any pixel's color and paste it in the clipboard, so if could figure a way to run the small application upon the button-click, take the value from the clipboard, and then close the application, that would work.
I need a function like a sumif, but rather than adding together numbers, I want it to pick the minimum. For example, if I had a list of prices for various products, I want the formula to go and find the lowest price for a particular product.
View 2 Replies View RelatedI'm trying to use VBA to pick records with a certain string in them using the following:
Code:
"SELECT * FROM tblIncidents WHERE tblIncidents.[Detail] LIKE %" & txtSearch.Value & "%"
But it's not working. It says it has a missing operator...
I have a table of data as below and would like a formula that will pick the number and name only (as in the need bit) - can this be done, they are on 2 seperate sheets.
DATA
Need
Number
Name
[Code]....
I have been using the RAND and Vlookup functions for the following without success.
I have 4 numeric Pools or Groups;
1. A1:A18 (1-18)
2. B1:B18 (19-36)
3. C1:C17 (37-53)
4. D1:D17 (54-70)
I also have E1:E20 (20 existing Numbers from 1-70)
I want Excel to select at random a total of 20 numbers
and place them in F1:F20.
I will specify a quantity from each of the 4 groups.
Eg.
In cell A20 I enter 4 to obtain 4 numbers from grp 1,
In cell B20 I enter 6 to obtain 6 numbers from grp 2,
In cell C20 I enter 8 to obtain 8 numbers from grp 3,
In cell D20 I enter 2 to obtain 4 numbers from grp 4,
However there must not be any duplicates between
E1:E20 and F1:F20!
My VBA code in Excel 2000 copies a worksheet and Saves it. The default path of the file location is held in a cell. The user defines this path through a userform. The userform simply has a text box and the user types in the path. This value is then transferred to the mentioned cell. What I want is for the user to be able to pick a directory from the user form rather than having to type it.
View 5 Replies View RelatedI have copied and pasted the formula off the ozgrid page using a 3 column table laid out as I believe it should be however I just get the # name? error message what I am not doing right. Please help. I just want to be able to generate a random name from a table of names, eventually I need to amend the formula to encompass a larger table but I can't get the example to work!
This is the page I have got the info from
[url]
I have uploaded the example I am using.
I've got a list of names in a column in excel, what's the easiest formula to pick a random name from that list? (guess I can use f9 to randomize)
View 6 Replies View RelatedWe get several sets of season tickets to various events to be distributed amongst several managers based on the mgr's headcount. So mgr1 may get 20 dates while mgr2 gets 5. All this info is calculated on Sheet1, but the only info that is important for this exercise is the mgr's names and how many dates they get. Available Dates will always be the same as Total Headcount.
Short of writing names on sheets of paper and drawing names from a hat, I would like to automate this process w/ a push of a button.
Sheet1 - this is where the managers are listed and their Headcount is calculated.
B C
10| Mgr1 Mgr2
12| 20 5
Sheet2 - this is where my ticket information is listed and will change based on different events.
A. B. C. D.
1| Date Row Seat Mgr
2| 1/1/08 H. 1-4. Mgr1 (20 times)
3| 1/1/08. I. 1-4. Mgr2 (5 times)
Ideally I would like to place a button on Sheet1 or 2 that would execute a code that looks at the range of Mgr's names and based on their hdct, inserts their name THAT many times in column D of Sheet2 like the above example.
This information should not change once it's assigned or unless the button is pushed again. The number of Mgr's names in the row on Sheet1 will change, but is never be more than 15, so the code must skip over cells that are blank if referencing, for ex. B11:P11.
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies View Related
