I have:
A1: Ph#
A2: Name
A3: Ph#
A4: Name
etc
I would like:
A1: Name B1: Ph#
A2: Name B2: Ph#
etc.
This is a large spreadsheet so I was hoping there was an easy way to make this change.
I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
I have 3 status sheets (about 300+ ea.) that I was given to sort out.
Information:
1) Column A: Number of items (i.e. 1 )
2)Columbe B: Rec'd Date + initials + no. of copies received, followed by notes (i.e. 021709,akb,01)
Since there is only one column with all the information together, is there a way to sort the attached sheet by initials? I don't know how to create a formula to pull all the date,mjg's; date,jac's; date,akb's; etc... into a separate table.
A: No. of items
B: Date,mjg... = Total no. of items
C: Date, abk... = Total no. of items
D: Date, akb... = Total no. of items
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx
how to move info from one sheet to another in the same workbook? Sheet one has several columns, including company name. Sheet two has company name and phone number. Not all the companies on sheet two are on sheet one (1500 on sheet 2, 200 on sheet one), so a sort-cut-paste won't do it.
If the company name is on sheet one, I need for it to get the corresponding phone number from sheet two and put it in the phone number column on sheet one.
[URL]
I have a workbook which serves as a master database at work. it contains two sheets: current residents of our facility (let's call this Sheet1), and those that have left/been discharged (Sheet2). It contains 87 rows and 34 columns of info.
I'm looking for a macro that will do the following:
When I click on a cell in any row (i.e. I need relative references) on Sheet1, the macro will (once activated):
1. Select the info between column D and column AH (inclusive) on the row where the selected cell is... i.e if I click on cell F4 before the macro is started, it will select the info from D4:AH4.
2. It will COPY this info
3. It will paste the info into the first empty row on Sheet2, starting from column C (i.e if the first empty row is 200, it will paste the info from C200:AG200)
4. It will then have a popup asking for: a. 'Date Left' and b. 'Reason', with two empty fields to input the info into. 'Date Left' is (obviously) a date value and 'Reason' is a text string. Once OK is hit on this popup, the Date Left will be pasted in column A of the same row (in our example, A200), and 'Reason' will be pasted into column B (again, in our example B200).
5. The macro will then go back to Sheet1 and delete the info that was selected between Columns D and AH inclusive (in our example, D4:AH4)
6. The macro will then save the workbook.
I have information in range(p10:Q10) on sheet DEMO! that I want to paste the Values only into a Separate sheet "LIST!" in columns(W:X). The row will vary and will be based on the cell: "DEMO!A3" and will match a cell in column A of "List!"
View 9 Replies View RelatedI am trying to make a "master" sheet where employees total calls for the day are recorded.
Each employee will be marking their calls each day which will be on an excel spreadsheet. i would like to then create a MASTER sheet that links to all the employees specific sheets so when they exit the sheet it updates the master, or it can be a live update if that is possible...
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data
[Code] .........
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
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]....
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
I am attempting to Organize some data; however, I have run into a problem. I will paste a sample of the data that I have and explain the situation....
View 9 Replies View RelatedI'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes
When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this: =left(A1,8)
The problem I'm running into is obtaining the dollar amount within the cell. For example:
A1 contains: 11112222 $1234.56 Sample, Name Sample Note
I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.
Further, the dollar amount varies from $1.01 to $10,000+
I need to sort information in a column containing both numbers and words. In the "asending" & "desending" it only gives two options to choose from. (none) & PartNum.
View 14 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I have three columns
"A" is item number: "B" is sales Date: "C" is sales price.
What I would like to do is search column A and when there are two of the same item numbers know what date they sold and for what price. I would prefer to build another table that has only this data.
The following code sets foundcell.address
View 2 Replies View RelatedI have data that I've converted from a different source, and the formatting doesn't always come out as I'd like it. For example, I have data that has two different values, and I need to split them into different columns. Is there an easy/quick way to do this?
15* 27016* 1324517* 2086518* 24943
I need a formula that will take the value of one cell and put it in another cell other than the one in which I am typing the formula. I can't think of how to do this.
I have two columns, A & B (no headers), of information. The columns are not identical, and column B has empty cells scattered throughout the column. For each empty cell in column B, I want to copy the same row of information into it from column A, as if I'm just scooting the information in column A that's on the same row as the empty cells in column B.
This is simple with a cut/paste, but when there's thirty empty cells, cutting and pasting takes time. (What's really frustrating is that I've figured out a few formulas that will copy the information from column A but all the information gets pasted in the column in which I'm creating the formula (say, column F, for example). THAT doesn't help me because the empty cells in column b are still empty!)
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here...
2
2
2
2
33
33
33
4
4
4
4
4
4
etc.
Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no.
so that it looks like this
2
2
2
2
46empty rows here.......................
What I'm having a hard time is trying to get information from column C to pull into a cell, for a specific student (column A), but only when the entry happens between two set of dates (column B).
Column A = Student Name
Column B = Entry Date
Column C = Entry
So in a different worksheet, the names of the students will already be on the report and each column following will have a date range of a week. So for column B I want the entry that only happened between "09/07/09-"09/13/09". No student will have more than one entry a week so I just want it to locate the entry and fill in automatically. This will repeat for each week after as well.
I am trying to convert 14 to 15 digits lengthy Binary value to number.
The formula =BIN2DEC(A2) is giving error #NUM
101111100001010
101111100001010
101111100001010
101111000011100
101111000011100
101111000011100
110001000001010
In column A I have Product Codes, They start at A3 and go down to A169.
I need to have a formula which i can drag across from CQ3 to DH3 which corresponds in the following manner.
CQ3 = A4
CR3 = A5
CS3 = A6
etc etc.
I then want to be able to copy the formula into another place, eg CQ20 = A21, CR20 = A22 etc
I have attached a screen shot.
Untitled-1000.jpg
I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.
I'm trying to add code to an existing macro that is working well to add a feature.
As it sits now, my code produces a worksheet tab called "Matrix" that has headers in row 1, a variable length list of employee names in column 1, and then a variable number columns with data crossreferenced by name in the body of the spreadsheet. Not every row/column intersection has data, some are blank if the ItemXname does not apply to that individual.
What I want to do is select all the cells on this spreadsheet, apply a filter, and then filter based on the selection of names in column 1. But I want to do this using VBA, where the macro runs, it asks the user if they want to filter on one or more names. If no, then it skips this part. But if yes, then the next thing they see is the filter window like they would see if you did this manually. After making their selections and hitting ok, the VBA routine would then take their selections (some finite number, but could be 5 -10 names) and process the rest of the macro accordingly.
I have a spread sheet with bunch of rows and columns. Columns goes all the way from A ... BB and rows from 1 to 40,000
The main focus is column Y and AC
If there is a value of FALSE found in column Y then macro should go to previous row and copy the value that contains in column AC and then move down to the next row, , where the value FALSE was found and that's in column Y and paste that value in column AC the NUMERIC VALUE
Below is the data ...