Microsoft Excel

134K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

create an new sheet Call DD with only the doubles digit

as shown

a3.png
here the file
29119277a_3N_to_DD_sheet.xlsm
0
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

I use a vb script to reformat CSV I receive from a vendor on a daily basis. I've started receiving a new batch of fields (columns) that I need to concatenate AND insert separators between values. Currently, my tortured method is below:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=RC[5] & "";"" & RC[6] & "";"" & RC[7] & "";""& RC[8] & "";"" & RC[9] & "";"" & RC[10] & "";"" & RC[11] & "";"" & RC[12] & "";"" & RC[13] & "";""

However, this results in a whole lot of orphan semi-colons whenever a column is empty. I've tried TEXTJOIN like so:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=TEXTJOIN("";"",TRUE,RC[5]:RC[16])"

but that failed.

Can somebody provide an alternative method that won't end up creating entries like this?

;;;;subject5;;subject7;;;;subject11
0
eliminated the follows after  the repeat key number repeat

as shown

b111.PNG3N_follows_to_dd.xlsm
0
Hi,
I would like a formula to detect a word within a string of words.
example:  Detect the word  'pear' from the following situated in cell C2     I picked a pear  (or)  I picked a plum
Return 1 if word exists and zero if it doesn't.
Many thanks
Ian
0
The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:

=A2&IF(B2<>"","-"&B2,"")&IF(C2&D2<>"","-"&C2&D2,"")

Open in new window


I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Andrea
Product_Numbering.xlsx
0
after the TOTAL sheet is created
ias shown in this sample
z222.PNG
then need an selector to select the  data  and create in SELECT  sheet as shown
align

z555555.PNG

file
29119153_to_improve_0_1_find_total.xlsm
0
create the find

the find total have to be exact match
then create the TOTAL sheet as show

z1.PNG
y77777777.PNG29119060a_to_add_find_total.xlsm
0
I need some help coming up with a present value formula to calculate what we call in our industry the true interest cost of debt or TIC.   In the attached file is the calculation produced by software that we use however it does not provide formulas.   I would like to come up with a spreadsheet that I can use to calculate TIC on my own.   About the spreadsheet that I have provided.   At the top is the solution to the cash flows below.   The dated date is the date that interest begins on the loan the TIC is the effective borrowing rate or the rate necessary to discount the amounts payable on the respective principal and interest payment dates to the purchase price (loan amount) received assuming compounded semi-annually.   The first column is the dates payments are made, the second column is the amount of debt paid each semi period and the third column is the discount column calculated by the software to come up with the TIC (discount rate) that equals the target amount above of $10,513,798.96.  I would like to setup a spreadsheet that will allow me to calculate on my on the TIC.  Meaning if I know the target amount, Dated Date, and semi-annual cash flows the spreadsheet will calculate the TIC that discounts those cash flows so that they equal the target amount.  I have tried using the IRR function but it did not work for me.  Hoping to get other ideas how to set this up using this example to check that it works by trying to recreate the answer.  I hope this make sense but …
0
Hello All.  First let me start by saying "TRIM" DOES NOT Work.

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.
The problem is, this data is in an Excel Cell.  The guy used the Alt + Enter to move to the next line after adding the comma.
When I pull it into a variable, I get one of two results.
" Paragragh 3.5.3"  or "Paragragh 3.5.3 "
It has a blank space before or after each one.  I am not sure it if is a Carriage Return or not.
I have not been able to get rid of it.  I need that term to use a search.  Because of the extra spaces, they are not being found even it they are there.

Fake Data:

PUNISHER1.2,3,
CYCLOPS,
VISION,
HULK,
GHOST RIDER,
THOR

  strLeftToCaptureCheck = Left(strToCapture, 1)
  If strLeftToCaptureCheck = "" Then
            MsgBox "Check"
  End If

As a test I used the code above to see if there was something there.
The result was strLeftToCaptureCheck  = "".  The "" is not a blank space.  It skipped over the message box.

Is there a way to find the first character position or just get rid of whatever those blank space are?
I never know if they are going to show in the beginning or the end.  I need a way to look at both ends.

Thanks
Nate
0
I would like be able to get same output as this workbook but without any data in column 'D
29118917_to_add_d_column.xlsm
0
CompTIA Cloud+
LVL 12
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I need a formula if it sees HD in a cell it counts it as .5 and the other characters are counted as 1.

V= 1
P = 1
S = 1
H= 1
HD = .5
TI = 1
0
I have a workbook with a "summary Sheet" and a "Masterdata Sheet", all of the data encoded in the "masterdata sheet" rolls up to the Summary Sheet. Is there any way to stop the automatic update of formula in the Summary Sheet while I am adding data in the "Masterdata Sheet"?


Thank you in advanced.
0
In column A are names and in column B I have emails. I then removed all of the emails that are present in my unsubscribe list. Now I have about 1K less emails. I want to now match the name with the final list of emails. Anyway to do that using an Excel formula or Macro?
1
select  0 and 1

as show  

 gg22222.png
then

gg11111.PNG
then

gg23333.png
the total
gg11111.PNG
2N_01_to_count.xlsx
0
On attached document, I have Column C of Date Values. On the next sheet titled "Pay Period" I have a table. This table has dates in column A and B.

On Sheet1 Column J, labed "Pay Period". I would like it to find which pay period Cell C2 falls in and result be the value of Column C of Sheet "Pay Periods". I am trying to use the following formula and it is not working:

=VLOOKUP(DATEVALUE(C2),'Pay Periods'!A:C,3,1)

Any help would be amazing. Thank you!
Project09202018.xlsx
0
Hi Experts,

Please help me with code. I have attached excel file. In that Column D has resource name, there if there name is repeating for more than 2 times shows some pop up or highlight the repeated.
Book1.xlsx
0
eliminated the follows after the key  number
29118287d--1-_to_add_after_key_clea.xlsm
0
Hey everyone,

Took a look at the EE forums but can't seem to find a solution.

I have a user who is unable to load the Solver add-in in Excel.  The add-in is selected, but when going to Data > Analyze, the Solver button is not fully there.  To be specific, the question-mark/arrow symbol is there, but it doesn't say Solver.  When clicking on the button, nothing happens.

The user is on Windows 10 with Office O365 2016 v.2215.  Our company has gpo restrictions on loading add-ins, but I am able to load and use it on mine and a test machine w.o problem.

He receives a msg about macros not being able to load, as did I, but on the test machines I received a prompt to trust the add-in, whereas he does not.

I removed and re-added the add-in, ran a repair on Office, upgraded Office to our current version, and replaced the Solver.xlam file on the user machine, no change.

The one thing I did not try yet is a different Windows profile.

Thoughts?

Thanks,
0
Cannot remove filter in Excel 2010. The option it is grayed out. See attached file.
Cannot-remove-filter-in-Excel-2010.pdf
0
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Every time I open Excel it also opens my

PERSONAL.XLSB

file.

What do you think is happening?

Thanks!

OT
0
key number is 44
then when it repeat  after the row 48

need to delete the follows numbers
a1.PNG
with an button
Eliminated_repeat_data.xlsm
0
I need to read  a number of comma delimited csv file from a specific folder and put each file into a worksheet with the name of the worksheet being the name of the csv file.  How do I do this using Excel VBA?
0
Hi
I am looking for an excel function or combination of functions to find out unique number of values by names. Also, i need to add a criteria in the function. Ie where some other field  only with blank cells.

Hope you understand. I can upload a file if you want to.

Thank you
A
aa.xlsx
bb.xlsx
0
In excel if you have long text in a cell that doesn't fit, it can overflow into the adjacent cell, as long as the adjacent cell is empty. I wonder if I could get the same behavior when I have a formula in the adjacent cell returning "". Unfortunately by default in this case, the text doesn't overflow.

I'm using Excel 2010
0
Dear Experts

In excel I need to rearrange the contents of a cell using a formula ... Each cell has 3 bits of information

job, company, dates (i.e. CEO, Microsoft, 2015-2018)

These are all all separated by ", " (Comma space) & I need the result to read

company, job, dates (i.e. Microsoft, CEO, 2015-2018)

Can anyone help?
0

Microsoft Excel

134K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.