I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....
I would like this formula to only pull names from the manager that is listed in the C column....
I am using the below code to locate the last column in various worksheets before pasting information. The problem is the column selected is either: Correct, Is a blank column with many blank columns in between it and the last visible text. The code also highlights all the cells containing text in some sheets. The results are the same for each sheet the code is run in i.e it is not varying.
View 4 Replies View RelatedI have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell.
Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19.
fig 1
Coloms A B C D
Row A 5 5 6 6
Row B 5 6 6 8
Row C 4 5 6 7
Row D 5 4 4 5
Total 19 20 23 26
fig 2
Coloms A B C D
Row A 1:00 2:00 3:11 4:01
Row B
Row C
Row E
Row F 1:19 2:20 3:33 4:27
Row F-A= 19 20 23 26
I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
Is there a easy way to find the last row of data in an Excel sheet using VB?
View 2 Replies View RelatedI need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
I have built a macro, but want the sheet to select all the rows until you meet a empty row then stop pooling etc..
See below, i am just selecting the rows when I recorded the macro. I want to tell the Macro, pool all the rows starting from row 7 going down until you meet a empty row.
What should I do to tell the macro stop pooling once you meet a empty row?
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/17/2008 by DHL User
'
'
Sheets("Air Freight Rates").Select
ActiveWindow.ScrollRow = 952
' Removed additional scrolling
Rows("7:2001").Select
I have two Excel tables.
Table 1 has Column A, with 500 unique names/codes listed.
Table 2 has Column A with the 500 names, plus 500 more mixed in (all unique). Table 2 also has columns B-G, with values corresponding to each of names in Column A.
Basically, what I'd like to do is filter out from Table 2 the 500 names (and their corresponding info in columns B-G) not listed in Table 1.
I've tried to look around, but most answers seem to be in reference to situations in which Column A has numerical values rather names.
I have a dataset in one work sheet. The data, cells, can be lovated by three identifiers. Each column has an identifier (month) and each row have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.
1234
10120011.812.275.031.6
10120022.272.113.724.09
10120032.263.243.232.65
10120042.061.792.384.59
10120054.012.373.655.16
10220011.211.920.814.4
10220020.571.331.853.3
How can write a formula/macro where I can put the three identifiers and retrive the data/cell into a new work sheet?
I needed to find "406" which is in A5280 copy the previous 160 rows X 3 columns to A5281.
Because of other factors involved I now realise it would be better to have the macro do the following.
1. Find "04/06" (in A5123)
2. Copy from 2 rows above this cell (A5121) down to (C5280)
3. Paste into A5281
Sub ACopy3()
Const intRowsToCopy As Integer = 160
Dim rngFound As Range
Dim Ro
Dim Col
Application. ScreenUpdating = False
Set rngFound = Columns("A:A").Find(What:="406", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Range(rngFound.Offset((intRowsToCopy - 1) * (-1), 2), rngFound).Copy rngFound.Offset(1, 0)
Application.ScreenUpdating = True
End Sub
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.
I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.
For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
I have the following data :-
SHEET 1
COL A_______COL B________COL C
V1990_______J100_________U1212
H2323_______Y999
U2222
SHEET 2
COL A_______COL B________COL C
U2222
I0000_______U8900________T67888
H2323
I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
I am in the process of comparing the data between 2 different sheets and the data on each sheet contains 2 columns of information. I am trying to find a way to compare one sheet to that same item on the second sheet and see if anything is missing, however the second sheet contains more information between the two columns and the rows are never the same during my comparison. I've tried Match, Lookup, Indirect, and if/then with an And functions and because the rows do not stay the same the data is not accurate. Here is a brief example of what I am working on:
SHEET 1
A B
1 Red Delicious Apple 125 Count Apple
2 Red Delicious Apple 125 Count Fresh Fruit
[Code]....
So in these comparisons, the names in Column A match identically, so if I try to say take the name from sheet 1 and match to that in sheet 2 and if the wording in Column B matches then put a "Yes" value. But you can see on Sheet 2 by the time it gets to rows 14, 15, 16, the Baby Food Chicken has a 3rd item in Column B in comaprison to Sheet 1, so any type of match doesn't work.
I am using Excel 2003.
Is there a way to do this?
a) run a code that will create a pop-up window with the question, "Can you show me where the CODEMAP.txt" file is?"
b) then the user will locate the file in a pop-up window and be able to click/select the file and there are "ok" and "cancel" buttons.
c) once the "codemap.txt" file is selected, the vba code will automatically change this file to "codemap.csv" so that it will open in excel (temporarily). This file can be placed anyway, even on the desktop because it won't be needed later. This is only temporary.
d) Then on the "codemap.csv" file, everything from sheet 1, cell b1 to cell b700 are copied and pasted to sheet two of this macro
The codemap.csv file can then be deleted
have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
View 1 Replies View Related I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
View 3 Replies View RelatedI am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I am trying to find a way to populate a column based on the changes in the data of another column...
For example, I want to fill in the code column....so when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to $37.51, want the code to change to 22.
CodeCustomer's Cost
$34.10
$34.10
$34.10
$34.10
$34.10
$37.51
$37.51
$32.50
$32.50
$33.48
$33.48
$34.10
$37.51
$37.51
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".
In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.
View 1 Replies View RelatedItem Quantity
A 2
S 7
D 3
F 6
I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc
The list I will pull this from is variable in length but in the
hundreds.
How to get the Column Alphabet based on the Data it contains or the Column Heading using VBA?
Is it possible to get the Column Alphabet using any Macro or any function that within a Module it can always take the New Column Name during Execution..
Example: I have certain Columns where I have Yes and No Tick using the Wingdings P and Y..
Now these Columns are alternately Placed and there are six columns in all and they are spread over 12 Columns as the Alternate COlumns is reserved for Manual Entries.
Now I intend to increase the Manual Entry Columns but the problem is that every time I do that I need to make changes in my VBA Code.
Is it possible that even when the Columns are Inserted or Deleted in between before or after these columns I dont need to change the Explicit references by changing some approach.
I am not fuly conversant in VBA but use it whenever things are not completely feasible with Formulas AFTER GOOGLING.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("X3:X13")) Is Nothing Then ' You can Change the range here
Cancel = True
[Code]....
As you can see the Ranges are Alternately Placed and there are about 6 such Columns with the above TICKS..
Now, how do I keep it FLoating so that even after changing the COlumn Location it does not need to get Updated i.e. X and Z Column Alphabets..
I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.
In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.
area (P)
phone (Q)
primary phone (R)
col S
col T
col U
[code]....