Need to trimming certain information contained in 1 cell and putting them each in a column. I could do left, mid & right but this time there is inconsistency in the content of the cell.
I've attached a file which will further give you an idea exactly what I want (formula to be in Column M to P).
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies View RelatedI have a workbook with two sheets. The first one, let's call it the Main sheet, will be used to keep track of the touring artists of a record label. The second sheet contains a list of show venues with their respective contact info. Here's what the two sheets look like :
Main sheet
mainsheet.jpg
List of contacts
listofcontacts.jpg
What I would like is row D of the Main sheet to autofill, according to the contact information contained in row B of the List of contacts, when I enter a venue name in row C of the Main Sheet.
In the attached workbook I have groups of numbers in Col G
In col H I would like to trim col G to just one each of the numbers where there are duplicates (as shown).
Each group of numbers will have a blank row between.
Cell E1 having 150 characters (letters), I want to trim first 45 characters in cell E1 and place it in cell B2 and remaining characters in cell E1 (ie. from 46th character till end) and place it in cell A3.
View 1 Replies View RelatedHow to trim when there are many lines in cell. The normal trim works only when we have one line in a cell.
View 9 Replies View RelatedColumn A
Row1 Orange-Apple-Pear
Row2 Orange-Grapes-Melon
Row3 Berry-Apple-Melon
Row4 Banana-Grapes-Melon
Row5 Orange-Grapes-Banana
Given the information above:
I want to find a macro that will auto filter on for example
"Banana" would filter to rows 4,5
"Orange" would filter to rows 1,2,5
"Apple" would filter to rows 1,3
NDL234561100015657
NDL546891100015658
NDL666771100015659
OSL342561200018756
OSL234441200018757
PIL44994 1500014566
NDL678991100015600
NDL555551100015664
above is col A which has all codes and second is col D which has respective numbers. i want help with some macro which can combine (seperated with a comma) all the numbers in col D based on first three letters of each cell in col A.
see like for NDL, i will have 1100015657, 1100015658, 1100015659, 1100015600,1100015664.
see like for OSL, i have 1200018756, 1200018757.
I need to remove a blank first line from a cell, attached two cells photo. Something with replace "chr(13)" or "chr(10)".
View 6 Replies View RelatedImagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
putting rows between different values in first column. The following puts 3 row inbetween each value in column "A".
View 2 Replies View RelatedI want to place this forumla "=IF(INT(B3/$U$1)=(B3/$U$1),1,0)" across my worksheet.
Now, I'm okay when dragging down as it works fine with the B3 changing to B4 and the U1 staying as U1.
However, I would like it so that when I drag it horizontally, the U1 becomes V1. I want it so that I can do this and it stays within the $. So when dragging sideways it becomes "=IF(INT(B3/$V$1)=(B3/$V$1),1,0)".
if this is possible and if so, how to do it?
I have a list of deals at current this is 500 odd cells long and increases every week
What i was doing was using a VLOOKUP to give me the details of the deal.
The problem with this is as follows
The deal ID was in column M,
What i was doing was copying the Deal Id and putting this into column A and then doing a vlookup.
What ive just realised is that this wont do what i need.
What this will do is return the value of the deal but only if the deal ID is matched in both Column A and M to give me the correct information.
Is there any way possible i can put in a deal ID, that will search the spreedsheet of all deals and return me all of the inforamtion of that deal
I am having problems finding a way to copy the contents of a cell and paste the content to a different worksheet on the first blank cell at the end of a column/row.
View 3 Replies View Relateddoes it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"
View 8 Replies View RelatedI have a worksheet in which one particular cell contains a certain formula. For security reasons, I wish to hide the FORMULA contained in the cell. However, the VALUE of the cell given by the above formula should always remain displayed for the user.
I want this to be done only for that particular cell. I tried hiding the formula bar,but it hides it for all cells.
I have some problem with conditional formatting, basically what I'd like to do is color the cells, based on the value contained in one specific cell. I'm not able to make a formula that suits my job.
Basically I want to color code a cell if the value in a specified cell is between x and y.
I've attached a sample spreasheet: MrExcel.xlsx
I'd like to display a formula contained in a cell within the worksheet. How can I do that?
View 4 Replies View RelatedI've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.
Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.
Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
I've been trying without success to hyperlink a shape in one workbook that refers to a website contained in a second workbook cell.
The hyperlinked shape is in the current workbook and the website address is in a workbook called "data source.xlsx", sheet 1, cell A1.
My last attempt was this - ('C:UsersLouiseDocumentsMA[data source.xlsx]Sheet1'!A1)
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
View 14 Replies View RelatedI tried to put the following formula into a cell using vba, but I received alot of errors. here is the formula I am trying to put into a certain cell and I will use a variable to replace the row number in the formula
=IF(AD323<>"Open","",IF(OR(AND($AI323=TODAY(),$AJ323<>"Done"),AND($AN323=TODAY(),$AO323<>"Done"),AND ($AS323=TODAY(),$AT323<>"Done")),"Yes",""))
How can I stop #DIV/0! in a cell which has a formula relating to a cell which will have a number inputted but at the moment is empty, without me putting a '0' in?
View 9 Replies View RelatedWhen I try to run the macro, an error comes up saying application-defined or object-defined error. The following code is the line in which I receive the error.
VB : tool.Worksheets("ALL").Cells(row1, "J").Formula = "='[TRB Database.xlsm]TRB Database'!$I$" & A
In my excel sheet I have a list of where they go, which are basically excel cells.. i.e. A1 is the top left corner.
What I am trying to figure out how to do is, is there a way that I can get excel to see "A1" and then put it in A1 of another sheet on the bottom. Thus I get the same grid I have on the wall, making a map of my things.
Also, I could just cut and paste, but the problem is I'm working on organizing the stuff on the wall, and if I do it the way I want to, when I move things, they should automatically be moved on the chart.
So basically I need it to look at a cell for the reference, choose text from another cell (which will be concaternated(sp) text), and then place it in the referenced cell in the chart sheet.
I have a row that is a percent of sales with this:
=(F4*0.2+G4*0.1)*I4
"I4 will either be 1.0 or 0.5"
Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.
I have a sheet with 2 colomns: client number and document number, as follows:
Client number Document number
1234 100012
1234 100042
4321 102233
4321 249123
4321 533213
4567 134123
7890 123451
7890 125675
7890 357353
I need to have a unique client numnber on 1 row with all his documents in one cell, as follows:
client number: document number
1234 100012, 100042
4321 102233, 249123, 533213
4567 134123
7890 123451, 125675, 357353
This seems simple but I can't figure it out. I want to grab the name of a worksheet and place the name in cell A1.
View 9 Replies View RelatedIt lists single clients on single lines, with various column fields that have autofilters (such as where th client came from, when they arrived, who is dealing with them, are they complete and so forth).
The final entry I need to include is a list of th policies (it is a financial business) they need us to consider, split into three columns of life policies, pensions and investments.
My problem is that I am unable to put more than one policy in each of those columns which can then be sorted through the filters.
How am I going to achieve this, yet still keep one client per line?
