Copy Data From Cell Only If Previous Cell Has Specific Text In It?
Mar 7, 2014
I'm trying to copy data from one excel sheet to another excel sheet. However, the data to be copied is dependent on the 'client name'.
To explain this further, in the first list I have a detailed report on our clients and the services provided to every employee of that company/client.
However, the sheet two only needs the names of the employees that belong to a specific client.
This can be done manually by setting a fliter on the name of the client/company, but I need to be automated. To ensure only that specific company/client company's employee name is copied.
View 2 Replies
ADVERTISEMENT
Jan 17, 2013
I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Useful information are included !!!
I'll give an example of a record...
Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
View 9 Replies
View Related
Sep 11, 2012
I have a huge data set as given follow and want to Copy a the value of treatment from the cell having Treatment description at some other sheet through VBA
'UPDATE [XYZ 1213]
SET TreatmentDescription = '000'
WHERE ( ("Abc" < 00) );'
'UPDATE [XYZ 1111]
SET TreatmentDescription = '093'
WHERE ( ("wer" < 00) );'
Like in upper code I want the output sheet 2 as Cell A1 = '000' and A2 = '093' and so on
View 3 Replies
View Related
Aug 29, 2007
I have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers. What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.
Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need. Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.
View 7 Replies
View Related
Sep 9, 2007
I want to copy formula from previous row to next cell when i enter something in perticular cell.
i.e
--Colomn A --- Colomn B -- --------Colomn C
1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false)
2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false)
3
4
5
Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on......
then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3.
I have also attached example file.
View 9 Replies
View Related
Mar 25, 2013
formula to copy a cell to a new cell if the previous cell meets the condition.
i have attached an example work book.
so what im looking for is on sheet 1 there is a table and cell G9 shows Apples. on Sheet 2 is a second table where the information is in slightly different rows.
I want to be able to [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column D into Sheet 1 Column H (next to Apples)]
Then i would like in Cell J9 to look something like [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column F into Sheet 1 Column J]
and i would like Cell I9 to look something like [When Sheet1 Column J = Sheet 1 Column L Then Paste Sheet 1 Column M into Sheet 1 Column I]
G9 H9 I9 J9 L M <-- hidden C D E F
Sheet 1 |Apples| 20 | Hot |Average | Hot | Average Sheet 2 |Apples| 20 | AP | Average
View 1 Replies
View Related
Jun 23, 2014
I am using the code below in Excel 2013.
Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
View 3 Replies
View Related
May 14, 2009
I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.
Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.
View 8 Replies
View Related
Jul 28, 2006
how I might 'trigger' a macro to run when a certain cell was filled with any value at all. I have the macro working fine (it's a simple copy and paste special) but I want it to run automatically when the cell A20 has a value in it.
View 9 Replies
View Related
May 31, 2007
From mySQL database, i am getting a ton of data that is all being inputted into a single excel cell. Within this cell, there are certain data points that i wish to obtain (Bank #, SSN #, Routing #). There are multiple difficulties in obtaining this information.
1. these cells contain the same fields, but different number of total characters (differing in names, addresses, etc.)
2. some of the data points (like SSN) are inputted incorrectly, so even though a SSN is only 9 digits/characters, I may have to output all 11 that the SQL database placed within the cell.
Can I get some help? I'm thinking of a search function/macro within excel, because all of the fields show up correctly.
something like (i know this doesn't work)
= or("cellA1" contains/finds/function SSN,return the 9 characters after "SSN ")
um...yeah. difficult to explain, i'm sorry!
edit: not sure what is wrong the file. I have attached a new one.
View 9 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Nov 4, 2009
I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.
LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
View 9 Replies
View Related
Apr 27, 2009
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub
View 10 Replies
View Related
Nov 7, 2008
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
View 9 Replies
View Related
May 9, 2008
is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...
View 2 Replies
View Related
Jul 7, 2013
I have a spreadsheet with over 6k rows, each with over 30 columns.
My focus is in 2 columns. ColumnA is formatted as General, ColumnB as Number.
ColumnA
ColumnB
05255088-0f15-48f1-a65e-affb6d583d35
1373064224475
05255088-0f15-48f1-a65e-affb6d583d35
1373064224475
[Code] ........
I need VBA code that will Fill the cell in ColumnB if it changes within the same ColumnA data.
So the output would end up like this. (Although the cell would be filled, not the text turned red)
05255088-0f15-48f1-a65e-affb6d583d35
1373064224475
05255088-0f15-48f1-a65e-affb6d583d35
1373064224475
[Code] .........
So it's pretty basic. If we simply remember the first ColumnA and ColumnB data, go to the next row, if ColumnA is the same, then ColumnB should be the same. If it's not, then fill with red, go to the next row, repeat. if ColumnA changes, remember the new pair, go to the next row and compare again.
I've thought about this from a scripting perspective, and imagine that something like this would do the job:
Code:
sub FindBadTermID ()
Dim row As Integer
Dim dataA as string, dataB as string
Set row = 2
newColumnALoop
[Code] .......
View 9 Replies
View Related
Feb 23, 2007
if cell in column E contains 'YTD', append value of cell in column D at the end with a space before it (example: " VALUE") to cell in column C. not sure how to go about this. i attached a one row example
View 8 Replies
View Related
Dec 21, 2013
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB:
="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
View 7 Replies
View Related
Jul 10, 2006
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
View 3 Replies
View Related
Sep 19, 2012
I have the word department followed by the department code in column A in an excel spreadsheet ie Department 50. Then say after 30 lines it may say Department 51 and so on. I want to copy down the cell containing the text "Department 50" in coulmn A until it reaches the word "Department",I then want it to copy down that cell ie "Department 51" until it reaches the word department again and so on. How I can do this?
View 4 Replies
View Related
Apr 9, 2012
see the example below...
ABCDE1blueblue2greengreen3redred4purplepurple
I basically want column E to check the cells from left to right and display the value that is in the first populated cell. For example, cell A2 is blank, therefore, it should display the value from B2. A3 & B3 are blank, so E3 should display the value from C3 and so on...
Is this an "IF" statement?
View 2 Replies
View Related
Oct 16, 2012
How can I repeat identical data in the previous cell. For example I have in Cell A1 - Marketing then I have in cell A2 to A3 same Marketing but it is not shown. Then I have in Cell A4 - Operations then Cell A5 to A8 same Operations but not shown. I need to have them all shown as in Column B,
A
B
1
Marketing
Marketing
2
Marketing
[Code] .......
View 3 Replies
View Related
Feb 9, 2010
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.
For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.
View 9 Replies
View Related
Oct 29, 2008
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.
View 7 Replies
View Related
May 16, 2009
This is probably really easy but I don't know what I'm doing wrong.
View 2 Replies
View Related
Mar 24, 2009
This is probably far easier then I am making it but I need to take the information that is in a textbox created through the control toolbox and copy and paste that information into a cell on different tab in the workbook. I have tried the infamous google and haven't been able to find much. The excel books that I have don't really touch on the control toolbox functions.
View 2 Replies
View Related
Jul 31, 2014
Here's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
View 11 Replies
View Related
Apr 3, 2008
I am creating a spreadsheet that when I type the product series in cell A1, it will automatically know to enter the corresponding catalog # into cell B1. I will be doing this for hundreds of product series.
For Example:
If I type AEP into cell A1, the worksheet will automatically enter #123 into cell B1,
If I type DEB into cell A2, the worksheet will automatically enter #458 into cell B2,
and so on...
I understand that I will have to create a master list with what series goes with each catalog #, but I am not to sure where to go from there?
View 14 Replies
View Related
Aug 3, 2009
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.
View 9 Replies
View Related
Oct 2, 2009
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
View 4 Replies
View Related
