Complex Text Formula Formatting
Sep 8, 2008
I am trying to generate a formula for formatting text that will remove all spaces, comma's etc. Examples as follows:
The Bank Job -> the _bank_job_small.gif
Picture This! -> picture_this_small.gif
War Games: The Dead Code -> war_games_the_dead_code_small.gif
Chacun son combat (Never Back Down) -> chacun_son_combat_small.gif
I've figured out a formula that works but it seems overly complex - it uses multiple SUBSTITUTE, SEARCH and REPLACE functions. Is there an easy way to remove a range of symbols i.e. ! : ( ' ?
View 9 Replies
ADVERTISEMENT
Apr 20, 2006
I have a sheet going like this:
CODIGOALBERTMARIODOUGLASEDYCARLOSJOHN
2818182768280
3858787818684
7828186768481
9877889928789
12828282788380
...
I need to colored the the max and lower value for every row (green for low and red for high) i did it well for first row with conditional formatting but i cant copy for the anothers ones and i dont want to do it row by row (i am so lazy). I did this for first Value and the copy format for the following values in row:
Formula Is =IF(ISBLANK(B5),FALSE,MIN($B$5:$G$5)=B5) (green color)
Formula Is =IF(ISBLANK(B5),FALSE,MAX($B$5:$G$5)=B5) (red color)
The reference must be Absolute cause another way results become wrong for another values, but thats the problem cause i cant use it in below rows.
View 2 Replies
View Related
Mar 15, 2008
i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:
=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99
What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05
View 9 Replies
View Related
Dec 15, 2011
I run reports that tell me when an individual is supposed to be in the office and when they actually log in, but this is a manual process where I have to go through several hundred people each morning and check. I have created a macro that pulls in their time in and out and makes there name appear as Last, First.
I run a seperate report that creates a schedule with headers and other information that is useless in this process. I have attached a dummy file and get this down to the last, first name and then the times in and such with no headers? I am also trying to keep the total number of people at the bottom in it's own cell.
I am at a loss as the file uses multiple spaces and not ,'s or ;'s etc.
FILE:
From: 12/14/11 Data TotalView 12/14/11
To: 12/14/11 Company ABC 13:45
Daily Start/Stop Unit Name RED
[Code].....
View 2 Replies
View Related
Feb 19, 2012
I have a column with data in the following (ideal) format:
texttextexttext/s2
texttextexttext/s2/c14
texttextexttext/s24/c2
and need a VBA solution to automatically split it across 3 columns, i.e
column1 column1 column1
texttextexttext s2
texttextexttext s2 c14
texttextexttext s24 c2
/s will always be present and will always be followed by one or more digits (no limit).
/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)
I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:
texttext3/12/2012text/s2/c2
text/textexttext/s2/c2
and even:
text/stext/ctext/s2/c2 (i.e. multiple occurrences of /s or /c)!!!
View 9 Replies
View Related
Apr 22, 2008
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
=IF(F23="Fail",0)*IF(COUNTIF(B22:B45,"Dissertation")=1,IF(VLOOKUP("Dissertation",B22:C45,2,0)>39,LOO KUP(F26,{0,40,50,60,70},{"No Dissertation","3rd","2:2","2:1","1st"}),"No Dissertation"),"No Dissertation")
isolation123
View 13 Replies
View Related
Jul 21, 2014
I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong
I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014
My Range called "DailyRates" is based per Year = 2014, 2015 ......
[Code]......
View 2 Replies
View Related
Feb 3, 2009
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
View 2 Replies
View Related
Jan 20, 2014
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
View 1 Replies
View Related
Jun 12, 2006
I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in
a different order. What I am trying to do is, If the account number from
worksheet one is found in the column with the account numbers in worksheet
two, I want to display the matching title for the account number that is in
the same row but different column in worksheet two.
So essentially (if acct # from column x in wks 1= an acct in number in
column m from wks 2, display the corresponding title in the same row of the
matching acct # found in column m). Is this possible?
View 10 Replies
View Related
Aug 9, 2012
I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level
1 = Level Below
2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number
if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
View 9 Replies
View Related
Jan 16, 2014
I've tried some of the basic formulas, but this is beyond me.
I have table A of 500 rows and need to compare it to table B of 2000 rows.
A
B
C
D
E
1
D6 - 1.01
Table with PC
3
Yes
2
D6 - 1.01
Bookshelf
4
No
[Code] .......
For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.
View 5 Replies
View Related
Nov 14, 2009
upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function
View 4 Replies
View Related
Oct 10, 2008
I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.
I basically need a formula which does the following to produce a total score:
[Total awarded score] / [Total available score of questions scored]
which equals
[(score <> "" / range)*weighting] / [if score <> "" sum weightings]
I've attached an example document where I think I'm getting close, just not sure if its possible.
View 4 Replies
View Related
Oct 11, 2011
I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.
The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.
I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.
How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?
Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007
View 3 Replies
View Related
Dec 31, 2013
I have prapared a formula for B2,B3 and C2,C3 and the reult came in B10,B11,C10,C11 and D10,D11,E10,E11.
From that relust i want to get the final result should be as follows.
B10 should again divided in to anil(in one cell) and 16/12/2013 11:33:50 (in another cell). This formula to be added in B10,B11,C10,C11 and D10,D11,E10,E11 formula.
I loooking for the final reslt, i have explain the in the excel. I reuired the final result with single formula from B2,B3 and C2,C3.
View 3 Replies
View Related
Sep 16, 2009
I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -
View 3 Replies
View Related
May 16, 2014
Let's say that I want cell B1 to autocopy cell A1, I will use =A1. However, let's say that the text in A1 goes as follows "text in bold and text not in bold" , what formula must I use so that it reproduces the exact same text formatting?
View 1 Replies
View Related
Jun 29, 2006
Is it possible to apply formatting to a formula in a cell when you are combining that formula with text? As an example, I want to format the following as a percentage: ="The result is"&" "&(a2/a1)
Currently, it is returning [e.g.] ...result is 0.5, instead of ...result is 50%
View 7 Replies
View Related
Mar 6, 2013
My formulas is
="Start Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,19,0) & "
End Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,20,0)
The results I want are Start Date: MMM-DD-YYYY End Date: MMM-DD-YYYY
What I am getting is: Start Date: 41537.7083333333 End Date: 41537.7083333333
So excel is give me the numeric values of the date instead of the formatted date. I tried to format the cell as date, but as I expected that didn't work because the cell also has text in it.
View 2 Replies
View Related
Jan 8, 2014
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies
View Related
Aug 19, 2008
I have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?
View 9 Replies
View Related
Jul 29, 2014
I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.
VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value
[Code] .....
View 9 Replies
View Related
Feb 25, 2009
I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.
This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The value of T9 is 2283.3 recurring.
There are two main parts to the SUM:
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)
...and:
+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The challenge is to insert another:
=ROUND(SUM(T9/1.5)/5,0)*(5)
in replacement of:
=SUM((T9/1.5)
I'm aware that the use of brackets differs between the two sections of the sum, but it is neccessary to allow the correct answer.
Is this too much for one poor cell to handle?
View 8 Replies
View Related
Jan 14, 2010
The function LOGEST fits to the equation y=b*m^x.
Does anyone know if more complex functions can be found in Excel?
Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.
View 14 Replies
View Related
Nov 27, 2007
I have started to develop my own userdefined functions for complex analysis. I have some problems, but I believe they will be solved soon.
The arrays are defined type complex with the
Type Complex
re As Double
im As Double
End Type
sub refleks
dim sa as complex
dim akd(150) as complex
.
.
..........................
View 9 Replies
View Related
Nov 12, 2008
I am trying to summarise training data in a spreadsheet as per the following layout:
A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter
3 Paul
4 Mary
The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).
The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):
A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2
12345 Jimbo
Peter Excel
Competent 2 3
54321 Nelson
Paul Access
Not yet Competent 3
Notes:
-The data can be up to 2000 rows long
-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)
-Result can only be Competent or Not Yet Competent
So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:
# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2)
Avh hrs per trainee = D2/B2
These were the easy ones.
What I’m having trouble with is the following:
For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier).
Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?
Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.
View 9 Replies
View Related
Aug 10, 2009
I have searched for awhile, but I am stuck on an equation that I could use help on. A little background, I am developing a staffing profile, which I previously was using pivot tables and then using a "=getpivotdata " forumla to pull the data. I then found the conditional sum formula and like it's capabilities as I wouldn't have to worry about human error if I forgot to update a table or if the data in the table wasn't updating correctionly. I am doing a 2 entity conditional sum but now I need to incorporate a simple if statement as well. What I am trying to do is add another formula stating that if the formula comes back with zero data, i would like to make the answer blank For example ... =if(D3="","") . The reason I need this, is I have a tab for each month. As it is Aug now, I have actuals spend until July but don't have anything yet for the remainder of the year. When I am calculating my averages for the remainder of the year, I don't want the data to be scewed by all the zero's. My question is, is it possible? I tried to add it and did the CSE trick and got an error message. I thought you guys could help. I hope I was clear. Please let me know if I was not and I can expand. Below is the formula I would like to add another if statement.
=SUM(IF('Jan'!$D$3:$D$1500="TD",IF('Jan'!$H$3:$H$1500="Supply Support",'Jan'!$AB$3:$AB$1500,0),0))/H$2
View 9 Replies
View Related
Sep 1, 2009
I have the following template: -
Rows C5:N5 = Months Jan - Dec.
B6 = Finance
B7 = Marketing
B8 = HR
B9 = IT
B10 = Operations
B11 = Legal
B12 = Service
B13 = Procurement
Rows C6:N13 = Randomly Generated monetary values (#.##). Note that some cells could be blank, indicating zero.
B20 = Finance
B21 = Marketing.........................
View 4 Replies
View Related
Feb 2, 2014
I've got the following complex issue:
...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0
I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.
Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:
Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.
For our example, this would then display:
Commodity 3
Commodity 4
Commodity 1
View 4 Replies
View Related
