[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Excel

135K

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

Hi Experts

Hope you can help

We ahve packing lists sent from our suppliers to check for carton compliance; data that is entered into Column H on the atatached example. This entry is normally entered as dimensions LxWxH and, for example, looks either as 50*30*20 or 50x30x20

In order to check conformity, I would like to put a formula in Column K to sum the carton dimesions entry however it is normally in text and as you can see above, either separated by a * or an x

Ive tried using VALUE and ISNUMBER with a combination of extracting the values to sum the entries (that I will then look up to the correct available sizes) but to now, I cannot find a suitable solution

I have attached an example

Any help would be greatly appreciated

J
EE_Example.xlsx
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Wanted to replace data from one column to another.

For example:
On the "OPEN" Tab - On column AB if there is something there then that should go to column J and what's in Column J should be in Column K.  The end result should look like what's in the "SHOULD BE" tab.  This should only happen when there is something in column AB

See attachment.
C--Users-lfreund-Documents-EE---COL.xlsx
0
Wanted to automatically add a thick bottom line when there is a difference in a numerical value in a column.

For example:
On column B there is a series of number  and I wanted a line starting with a new number to seperate it from coumn A to column I.  for Example Row 63 there is a thick bottom line for the number 4 and one at row 65 for the number 5.

The data is linked to an access database so the data always changes day to day.  So the code would delete the the thick bottom line and add new ones accordingly.

See attachment for example.
C--Users-lfreund-Documents-EE---LIN.xlsx
0
The zip file is a folder containing 6 files.

Three originals and three examples of the required result.

In each of the original files:
la_162.csv
la_163.csv
la_164.csv

column A contains a heading in A1  and a list of numbers from A2

The requirement is to be able to highlight all original files in the folder and run a script that
1. includes the number from the file name in column B as per the example for each file and
2. renames the amended file to include the heading from Column A in that file name.
EE-folder.rar
0
Pivot - even with Data Model is not working correctly.
I have created a workbook that contains three tabs - each tab contains a table.
This file will be loaded into Sharepoint so users in the target group can add data online - so no VBA is allowed.

The three tables are as follows:
1. Customers - simple list of customers with a couple of values that categorize the customer (Region, Market, Industry for example) - and also a $$ value of that customer. This is added to on a regular basis as we work with new customers .
2. Issues - a simple list of typical issues that are encountered by customers. This list rarely changes - but it can be added to as new issues are identified. This list also contains categorizations of the issues (Category and Sub Category)
3. Customer/Issue Cross reference - this table is a simple cross reference. Customer to Issues encountered.

When we have a new customer with issues - we add the customer to the customer table (so we only define the customer and their attributes once), and then we access the Cross Reference table - and then define potentially multiple rows - the customer will be repeated for each issue that we wish to list. So if a customer encountered 3 issues there would be three rows on this table - the customer would be listed three times (in the first column) and then each issues would be listed against the customer in a separate row.
The data is very simplistic.
However - here is the issue I am having. I have created a Data …
0
This  is follow up question to VBA solution by Expert Fabrice Lambert.

this could be easy, but I spent more than an hour to figure out how to make the change but could not do it.
I need to change the criteria that this time in Column A, instead of full path, it just shows the name of that file as shown in the screenshot example.
and in Column B,   the crtieria " nothing found remains unchanged, but   if there is any of the criteria words is missing, it should list those ones that are missing. and  when it found all searched words, then it should return all found as shown in the example screenshot.

Thank you very much for your help.

sdfs.jpg
Option Explicit

Function GetFolderDialog(strPath As String) As String
    Dim dialog As Office.FileDialog
    Set dialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    With dialog
        .Title = "Select a path to the .htm files"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show = -1 Then
            GetFolderDialog = .SelectedItems(1)
        End If
    End With
End Function

Public Sub Main()
    Dim basePath As String
    basePath = GetFolderDialog(ThisWorkbook.path)
    
    If basePath <> vbNullString Then
        Dim results As Collection
        Set results = New Collection

        SearchHTMLFiles basePath, results
        

        Dim wb As Excel.Workbook
        Set wb = ThisWorkbook
        
        Dim ws As Excel.Worksheet
        Set ws = 

Open in new window

0
Is it possible to create waterfall chart in excel 2010?

If yes, can you show me how?
0
I am looking for a formula to determine what week range the numbers are late by eg, within one week,two weeks etc.
weeks-overdue.xlsb
0
I pull data from a .CSV file that I dump into a multi-dim array.
I reformat array (into the final array) and then create an excel file and set all the column/row formatting.
In the final process, I dump parts of the array into structures columns.
I am using using Excel = Microsoft.Office.Interop.Excel;

The interaction with Excel seems to take longer than it should.

Using Interop to communicate with excel the most efficient/fastest way?

    int j = 2;
    oSheet.Range["A7:P" + rplUpdatedData.GetLength(0)].Borders.LineStyle = XlLineStyle.xlContinuous;
    for (int i = 7; i < rplUpdatedData.GetLength(0); i++)//i for Column index.
    {  

	oSheet.Cells[i, 1].value = rplUpdatedData[j, 0];
	oSheet.Cells[i, 2].value = rplUpdatedData[j, 3];
	oSheet.Cells[i, 3].value = rplUpdatedData[j, 4];
	oSheet.Cells[i, 4].value = rplUpdatedData[j, 15];
	oSheet.Cells[i, 5].value = rplUpdatedData[j, 24];
	oSheet.Cells[i, 6].value = rplUpdatedData[j, 6];
	oSheet.Cells[i, 7].value = rplUpdatedData[j, 26];
	oSheet.Cells[i, 8].value = rplUpdatedData[j, 8]; 
	oSheet.Cells[i, 9].value = rplUpdatedData[j, 28];
	oSheet.Cells[i, 10].value = rplUpdatedData[j, 30];
	oSheet.Cells[i, 11].value = rplUpdatedData[j, 12];
	oSheet.Cells[i, 13].value = rplUpdatedData[j, 26];
	oSheet.Cells[i, 14].value = rplUpdatedData[j, 16];
	oSheet.Cells[i, 15].value = rplUpdatedData[j, 1];
	oSheet.Cells[i, 16].value = rplUpdatedData[j, 2];

	j++;
    }

Open in new window


Thanks
Jed-
0
excel 2010
Need to add a field in a formula if i can.
I may need a function/routine ?

=IF(S4<4.0%,"FVP approval",IF(S4>4.00%,"Update Quote", "RSVP/Director approval"))

What they have added to me is the following matrix:

Column K is where the  $  value is stored

              <$250                     $250-$1000                         >$1000
<4 %      Update quote      RSVP                                FVP
4% - 8%      Update quote      RSVP                                RSVP
> 8%      Update quote      Update quote                Update quote

So if i wrote this out:
IF  S4   <4% AND k4 <  250$ then "Update Quote"
IF  S4   <4% AND K4 BETWEEN   250$ AND $1000  then "RSVP"
IF  S4   <4% AND k4 >  1000$     then    "FVP"


This may seem to work but, Nesting alot of these statements..Dont think i can do ?
=IF(AND(S4<4,K4<250),"UPDATE QUOTE","")

etc....



All in One  function  ? or maybe a vba routine ?


Column N is where the Text is displayed

Thanks
fordraiders
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Excel - I have two columns with duplicate information in them. However I need to find differences by line.
Example:
Row    Column  E   Column G  /   I need to find the rows where column E and column G are not the same.  > rows 3 and 5
1               A                       A
2               B                       B
3               D                       A
4               E                        E
5               A                       B
6               B                       B
0
Excel Table Support.
I have created a Table in an excel worksheet where users will be expected to add new rows manually. Note that this document will be a shared document on Sharepoint - so I cannot use any VBA etc.

I have created a number of other tables in the workbook that act as Data Validation lists. The data validation etc is working fine.

However - this is my problem. The user only has to enter two pieces of information in the table - and both pieces of data are entered into the first two cells/columns of the table. Normally this works perfectly - you just go to the first row under the table and begin typing - and when you tab to the next cell - the table automatically expands to add the row etc.
However, the first column (and second column) are both controlled by a Data Validation list.

The issue is - if you click on the cell below the table - the data validation is not available yet (as the table has not expanded) - so the person cannot "Select" from the list. If they attempt to type something - if they get it wrong and tab to the next column then they will of course get an error - because the table will expand and the cell now contains validation - so they either have to retry or cancel - both of which "remove" the new table row added - so the validation goes away - so they cannot select from the drop list (as it no longer exists on the cell).
So I am kind of between a rock and a hard place. I want to make it as simple as possible - basically being able …
0
This is a follow question on solution given by  Fabrice Lambert and Sam Jacobs

this code below works and searches for items in sheet2 and if any of them is found it lists it, if nothing found then it returns nothing found.  Now, i have to change the criteria.

The VBA needs to look for all items in sheet2  if even one of them is missing then it should return "one or more item is missing"  and only when it found all of the searched value then it should list it as shown in the screenshot.  

Thank you for your help.

gddfg.jpg
sadas.jpg

Option Explicit


Function GetFolder(strPath As String) As String
Dim folder As FileDialog
Dim sItem As String
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
Dim ret As Integer
With folder
    .Title = "Select a path to the .htm files"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show = -1 Then
        GetFolder = .SelectedItems(1)
    Else
        GetFolder = ""
    End If
End With
Set folder = Nothing
End Function

'' You could also use

'  Dim basePath As String
'    Dim fd As FileDialog
'
'    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'    With fd
'        .Title = "Select Base Path"
'        .ButtonName = "Select"
'        .AllowMultiSelect = False
'        If .Show = -1 Then
'            basePath = .SelectedItems.item(1)
'        Else
'            Exit Sub
'        End If
'    End With
'  or simple basePath = InputBox("Enter your Path",

Open in new window

0
Hi. I am trying to remove characters from the right side of a cell in excel using vb.net so that I will be left with a date value. Here is the cell value:
REPORT DATE:07-Sep-18

Here is the code:

        'set up initial column
        strColumn = "J"
        'set up initial row
        intRowID = 2
        'Set up range
        strRange = strColumn & intRowID
        strStringDate = ForcastWorkSheet.Cells.Range(strRange).Value
        strDate = strStringDate.TrimStart(strStringDate.Length, -12)

Open in new window


The error message is "Integer values cannot be converted to Char"

Thank you in advance for any help with this.

Valerie
0
I need help with changing two thing in my code.

one of them is  instead of hard coded path.  
Const basePath As String = "c:\Users\Skylar\Downloads\Factory"  

Open in new window


I need help to modify this, so that instead of this fixed path.  I would get a prompt to select the path from the directory. I searched google but got confused with too many different answers that i could not put to use.

the second help i need is that my current range is static from A2 to A13 i want to change is to dynamic so that if there is more value it expands the range and if the values are not there then it collapse the range.
I do not know how to change this little code below
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets(2)
    
    Dim rng As Excel.Range
    Set rng = ws.Range("A2:A13")

Open in new window

0
What is the best source Book or video to learn Power Bi?
0
Excel Slicers -  Is there a way to use a slicer with an option of "Contains _______" ?
0
I have an excel 2013 application with  timer code that fires every 1 second. My code within the timer function executes some functions one of which counts seconds. After 60 seconds have elapsed it writes data to a sheet showing the date/time. Even though the timer is executing every 1 second, by the time it has iterated 60 times, it is adding 6 seconds to each record. How can subtract 6 seconds from the now() function ?

The following shows data written after each 60 second iteration. Each record should end with 48 seconds

12:20:48 PM
12:21:53 PM
12:22:59 PM
12:24:05 PM
12:25:11 PM
12:26:17 PM
12:27:23 PM
12:28:29 PM
12:29:34 PM


This is the code in the module to increment time by one second:

Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
0
Hello,

The following formula is being used in column B:  =IF(A2="CHANGE_GENERIC_OBJECT",IF(C2=FALSE,IF(ISBLANK(E2),"LOA","WK LOC"),"POSITION/ORG"))

So the outcome is either POSITION/ORG, LOA, WK LOC OR FALSE.  Instead of FALSE I'd like the return to equal whatever the text is in column A.  For example, if A2 shows TERMINATE, I'd like the text TERMINATE to appear in B2 instead of FALSE.  

Any recommendations on how I can change this formula?

Thanks!
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Dear Experts,

Please Guide me Over Below Code How can i Speed Up i Made it False Screenupdating and xlcalculations manual but on status bar while running code it shows calculating and code execute slowly Please Guide the best you understand out of my question.

Regards,


Sub GL1_Code()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If ThisWorkbook.Sheets("GL").FilterMode Then
ThisWorkbook.Sheets("GL").showalldata
End If

ThisWorkbook.Sheets("GL").Unprotect ' unprotect Protected area

Dim rRow As Range

Dim Lrr As Integer
Lrr = ThisWorkbook.Sheets("GL").Range("A" & Rows.Count).End(xlUp).Row

For Each rRow In ThisWorkbook.Sheets("Inv").Range("B9:B" & ThisWorkbook.Sheets("Inv").Range("B" & Rows.Count).End(xlUp).Row)

If rRow.Value > Empty Then

If ThisWorkbook.Sheets("GL").Range("A" & Lrr + 1).Value = Empty Then

'SERIAL NUMBER AT GL LEDGERS
ThisWorkbook.Sheets("GL").Range("A" & Lrr + 1).Value _
= ThisWorkbook.Sheets("GL").Range("A" & Lrr).Value + 1
'DATE AT INVOICE
ThisWorkbook.Sheets("GL").Range("B" & Lrr + 1).Value = CDate(ThisWorkbook.Sheets("Inv").Range("K5").Value)
'
ThisWorkbook.Sheets("GL").Range("C" & Lrr + 1).Value = ThisWorkbook.Sheets("Inv").Range("C4").Value

ThisWorkbook.Sheets("GL").Range("D" & Lrr + 1).Value = "BR"
ThisWorkbook.Sheets("GL").Range("E" & Lrr + 1).Value = ThisWorkbook.Sheets("Inv").Range("B4").Value
ThisWorkbook.Sheets("GL").Range("F" & Lrr + 1).Value _
= rRow.Value & " , " & rRow.Offset(0, 2).Value

Open in new window

0
I would like to highlight a blank cell that is part of a combo list in Excel 2013

I'm making a form for users to complete and I want to highlight the cells that the user has to populate information into.  One of the cells contains a drop-down list.  The first cell in this drop-down list was intentionally left blank.  When the form isn't filled out, this would be a blank cell.  This is the cell I would like to highlight a color until the user has selected the appropriate item from the list.  How can I accomplish this?  In my attached spreadsheet the cell I wish to highlight if blank is B2
Dummy-Ticket.xlsx
0
  1. With the intersect, how can I prevent the code from being activated if there is no Site? (The commented code affects the whole worksheet.)
  2. How can I refer to Selection.Offset(0, -1) by the column's name, Site?

Dim sSite As String
If Not Intersect(Target, tb.ListColumns("Variable").DataBodyRange) Is Nothing Then
' If Intersect(Target, tb.ListColumns("Variable").DataBodyRange) <> "" And Selection.Offset(0, -1) <> "" Then
    Cancel = True
    sSite = WorksheetFunction.Substitute(WorksheetFunction.Index(Range("Sites[[Base]]") _
    , WorksheetFunction.Match(Selection.Offset(0, -1), Range("Sites[[Site]]"), 0)), "*", "" & ActiveCell.Value & "")
    ThisWorkbook.FollowHyperlink (sSite)
End If

Open in new window

0
I am trying to utilize a SUMIFS function in the simplified table below.

Right-Test.png
In cell D2, I want to show the sum of all items in column D where column C equals "t" and column B ends with "?".  The formula I am stuck on is:

=SUMIFS(D:D,$C:$C,"t",$B:$B,RIGHT("~?",1))

Open in new window


I know the column C condition works properly, but I am missing what is wrong the the column B condition.  The right function seems to work if I use "RIGHT("AB",2)" but not with any single character.  Could somebody kindly point me in the right direction, please?  Thank you in advance!
Right-Test.xlsx
0
I am looking for a couple of formulas in excel to extract the date that someone joined their current company and where they joined from in the 2 adjacent cells

The data is all in 1 cell and the data lists their employer, job title and dates chronologically and each job is separated by a line break. EG:

•  Company 3 – Job 4 (May 2018 – Present)
 - Job 3 (Mar 2017 – May 2018)
•  Company 2 – Job 2 (2005 – Feb 2017)
•  Company 1 – Job 1 (May 2000 – Jun 2004)

A change in company is always preceded by a bullet point and 2 spaces.  They may well have been promoted in their current company and where this is the case there is no bullet point just a space hyphen space then the details (as per line 2 in the above example)

So in the above example this person joined company 1 from company 2 in March 2017 (he also happened to be promoted in May 2018, but that’s not the date we’re after).

Can anybody help?
0
Wanted to calculate net amount based on what's been delivered and what's outstanding.

For example on the attachment:

I wanted to calculate net amount on column F based on Columns B and Column C:

If column B is 0 or blank and column C is 0 then the net amount is the amount on column E that should be on column F.

If column C has a number then it's column C times what's in column D that should be on column F.

I did the calculations manually on column F to show what the amounts should look like.
C--Users-lfreund-Downloads-Calculat.xlsx
0

Microsoft Excel

135K

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.