Macro To Return Value By Combining 2 Cells & Finding Mat
Dec 15, 2006
I have a step in a very long macro to populate a sheet (SheetMaster) with the course completion status based on another sheet (SourceData) in the same workbook.
On "SheetMaster" Col E starting with row 2, I want to look at whether a concatenated value of E1 + A2 is in Col G in "SourceData". The macro should run until it gets to a blank row in Col A.
Here is an example to help illustrate the logic:
E1 = Ethics101
A2 = 123456
A3 = 654321
If Col G in "SourceData" contains "Ethics101-123456", E2 = "Complete", if not "Incomplete"
If Col G in "SourceData" contains "Ethics101-654321", E3 = "Complete", if not "Incomplete"
...and so on until it gets to a blank row in Col A...
View 9 Replies
ADVERTISEMENT
Feb 8, 2007
I have rows of data in one column. I want to combine them all into one cell and have each row on a new line.
I've tried =A1 & char(10) & A2, (and a bunch of other char(#s) for carriage return) but it just shows an open box in place of the char().
View 12 Replies
View Related
Jul 28, 2009
I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.
View 5 Replies
View Related
Sep 16, 2008
I have a row of cells with names. Alex, John, Martha, Jim, etc.
Each person has a list of data under them. I have a master column of data. Depending who is on duty today, I want the macro to find the person and then copy paste special the master column over the person column.
Thus, in the macro code where it says
Find(What:="John",
I want it to refer to a specific cell. I.e. what:=$E$4.
Then it must go to the cell it found and copy paste the info in.
View 14 Replies
View Related
Feb 12, 2014
provide me a macro to give a particular message or Cell Name when the cell is blank. I have to check around Eight columns and the starting row is A10. Moreover the sheet can have different numbers of row at different time like some time only 50 persons data will be there and some times 60 or 85 like this but the maximum will be 300. So if the 50 persons data is there than it should not warn about row 51 onwards.
View 1 Replies
View Related
Sep 15, 2014
Need Excel Macro or Formula for finding duplicated cells more than 5 times and copy them into new sheet.
For Example;
apple
orange
apple
banana
apricot
[Code]...
OUTPUT in new Sheet will be
apple 5
orange 5
View 1 Replies
View Related
May 17, 2013
I have a form in excel (very simple) that I need to put on the web - People can then fill out certiain bits of the information and the idea is that they then click on the button to email it back to me. Ie complete it there and then and email it straight away so that they don't need to save it etc.
I can't for the life of me work out how to do this! I've managed to get the macro to open an email with the correct details on but not paste in the information so am at a complete loss and appear to be going round in circles!
View 2 Replies
View Related
Sep 26, 2013
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
View 2 Replies
View Related
Apr 23, 2014
I have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
View 4 Replies
View Related
May 22, 2008
Here is a little formula I working on:
=IF(MID(A1, LEN(A1),1)="i", "x", "")
As it is, it would return an X if the last value of a string is "i"
What I want to really do is check if the last value is a " qoute but it seems you can't put """ or you get an error -- example: =IF(MID(A1, LEN(A1),1)=""", "x", "")
View 9 Replies
View Related
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Feb 7, 2012
I have an array that is 60x100 (column x row).
I'm trying to search for a value in that array and return the value on the same row, but shifted to the right one column.
Example: If my value is found in D63, I want to return the value in E63.
I've been messing with Index, Match, and Offset, but I can't get anything working.
View 4 Replies
View Related
Jul 13, 2011
I am trying to use ADO via VB6 to find a specific input in row A of my excel sheet.
I then want it to return the Column and Row to me separately.
I have a code I already use for returning specific values from cells:
Code:
Function Look(ClosedWorkbookFullName As String, _
SheetName As String, RangeAddress As String) As Variant
Dim conn As Object, rs As Object, SQL As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
[Code] ......
Now I want something like this I can use as a function, but it obviously needs tweaks.
View 1 Replies
View Related
Sep 28, 2009
I know a simple formula would 'almost' do what i want...but i was hoping for a simple feature....
i have several text cells that i want to combine into one cell.....
so a formula like =A1&B1&C1.... would work fine....but is there anyway to present this data a little fancier? for example separating each cell entry after a "break" or something....
View 9 Replies
View Related
Dec 20, 2007
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
View 9 Replies
View Related
Apr 29, 2009
I have two columns, Brief Descriptions and Detailed Descriptions. In many cases they are different and I combine the two for one longer description; however in certain instances the two columns read the same. Is there was way to combine the two cells where any repeat text is only expressed once?
View 3 Replies
View Related
Dec 29, 2009
I'm trying to combine 2 cells: 1/1/09 and ABC. However, when I combine them the date keeps changing to a number. Is it possible to keep the date format?
View 2 Replies
View Related
Sep 17, 2009
I am trying to combine multiple cells into 1 cell per row.
I would have "A" column empty, then combine "B", "C", and on into the "A" column.
Each cell value will be separated by a space or any special character I designate in the macro
I have attached an example which the output is separated by a space. Sheet1 has the original file and Sheet2 has the desired output.
View 9 Replies
View Related
Feb 13, 2009
I'm looking to take words which are originally in their own separate cells and "combining" the words all into one single cell.
Is there a function that will be able to do this for me?
View 2 Replies
View Related
Jul 27, 2009
I have a list of dates in column A as follows:
11/1/2015
5/1/2012
8/8/2013
10/22/2015
4/1/2014
4/15/2014
In column B I have set of data as follows:
C 2010@101.0
C 2011
C 2011@101.0
C 2012@101.0
C 2013
C 2013
C 2014@101.0
I need to combine the day and month only in column A with the year (in positions 3-6) in column B
As an example I have "11/1/2015" in column A and "C 2010@101.0" in column B. The desired result in column C is "11/1/2010".
I have tried using the left function in column A with the mid function in column B but haven't had any luck so far.
View 2 Replies
View Related
Jan 11, 2010
I am combining information from two different cells to one cell using cell reference such as,(=B4&"/"&F5),B4 being a number and F5 the date. So my question is how to get the date to show as a date value and still have the other value remain unchanged. I have tried to use formating but will not work to keep the date. An example would be
View 2 Replies
View Related
Apr 17, 2006
I have a spreadsheet with a name spread over 3 cells
A1 = MR
B1 = JOHN
C1 = SMITH
i want to create a macro or a formula that combines all of the data in these cells into the first column so that in column A1 it would read MR JOHN SMITH.
View 5 Replies
View Related
May 9, 2007
I want to combine cells from two adjacent columns in this way: a1 is combined with everything in column b, then a2 is combined with everything in column b, etc. So that I have a1b1, a1b2, a1b3, a1b4, a2b1, a2b2, a2b3, etc.
View 9 Replies
View Related
Jun 28, 2007
I'm trying to write a macro that selects file A, copy some particular columns in file A, copy them into a new workbook, then repeat the process for file B, and copy and paste the columns into the same working workbook. Does anyone have any suggestions on using any one of the workbook, worksheet functions to do so?
View 9 Replies
View Related
Oct 23, 2008
I was wondering if you could help me combine a load of Macros (Theirs about 20, but I've only put two here)The code I have is this...
View 10 Replies
View Related
Feb 6, 2007
I have three sheets within an excel file and I wish to run a macro to pull the results into one sheet. The individual sheets may vary in size and length so I want to copy all opf the used- range(?). I attach an example of the three sheets and an end result sheet.
View 4 Replies
View Related
Jan 30, 2013
I am setting up a new chart of accounts, with the format xxx-yyyy, where xxx is a three digit department number, and yyyy is the expense account. I am building a spreadsheet to populate the new chart of accounts to be uploaded into our accounting software. I would like to have a cell that takes the xxx from one cell, adds a hyphen, and combines it with the yyyy value. The result would be xxx-yyyy format.
View 3 Replies
View Related
Nov 21, 2013
I have the below data that I need to concatenate, merge... I'm not sure.
The data looks like this currently:
So that it reads in one sentence, e.g. Northumberland; Newcastle; North Tyneside; South Tyneside (note - no "." or ";" at the end of the string).
I had come up with this formula -
=IF(A11>0,A11&"; ","")&IF(B11>0,B11&"; ","")&IF(C11>0,C11&"; ","")&IF(D11>0,D11&"; ","")&IF(E11>0,E11&"; ","")&IF(F11>0,F11&"; ","")&IF(G11>0,G11&"; ","")&IF(H11>0,H11&"; ","")&IF(I11>0,I11&"; ","")&IF(J11>0,J11&"; ","")&IF(K11>0,K11&"; ","")&IF(L11>0,L11&"; ","")&IF(M11>0,M11&"; ","")&IF(N11>0,N11&"; ","")&IF(O11>0,C11&"","")
Which works fine if there is a value in cell O, but if not, then a semi-colon appears at the end of the string. It also seems incredibly clunky.
Basically, I'm struggling (being a total n00b) to get the semi-colons in the right place, blank cells to be skipped or not included, and for there to be no semi-colon after the last value.
View 4 Replies
View Related
Mar 4, 2014
I currently have a column with each cell containing alphanumeric data of a filename.
The majority of cell data looks something like this.
"AB_XYZ_0408_00700.doc"
"AB_XYZ_0408_00708.doc"
"AB_XYZ_0408_02200.doc" etc
**Note the numbers "00700" are always 5 digits long also note the extension is always ".doc" (4 characters long). However the other parts, AB_XYZ etc vary in length based on the worksheet.
I need to ADD or Subtract "+1" or "-1" from the 5 digit number for example make "00700, into 00701"
I currently tried to separate the data into columns which provides me with the number 00700 isolated in its own cell and then I simply add or subtract 1. In that isolated cell I am able to add "leading zeroes" and it works great.
The Problem is that when I combine the separated cell data "=A1&B &C1" with the New number (in this case 00701") the problem that I run into is the leading zeroes do not follow over to the combined cell and I am left with a final filename like this "AB_XYZ_0408_701.doc" - That is missing the leading zeroes.
Also if you have a more advanced formula that could simply add and subtract "1" from the 5 digit number that would be ideal.
View 2 Replies
View Related
Feb 13, 2007
i need to get an equation correct for a scheduling application
assume $f24 is a task frequency and U24 is our current cell location
=IF(OFFSET(U24,0,(-$F24+1),1,($F24-1))"","","X")
i am asking if (the range of cells to the left of current cells (range being task frequency -1 columns wide) are empty - and if so - put an x - if they are not empty - then leave it balnk.
i can get it to to work for a 2 weekly schdule but need it to work upto 52 weeks
i have a simple spreadsheet with the problem clearly defined for anyone who can help
- beer in it for anyone who can help - or can pay via paypal if required
View 9 Replies
View Related
