Microsoft Excel

131K

Solutions

37K

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

Please see attached sample. Using sheet1 as the source, I would like a piece of vba to move each row to an output sheet. For each row it would check cell R to see if the series number had changed. When it detects that it has changed it will need to position back to the rows preceding the change in column R value and overwrite cell A for all rows that have the same value in cell R with the very last value of cell A that it finds in the series. Probably easier to understand when looking at the sample output.

Each entire row including the header row should be copied to the output sheet. There will be 1000's of rows in my real data.
sample-docseries.xlsx
0
New benefit for Premium Members - Upgrade now!
LVL 9
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

have this code working great
but need adjust for just 2 num  example  99  or 66 ect..
conditionalfomatting--2-_all4n_with.xlsm
0
I have a large pdf file of that has formatting as follows:

Balance Brought Forward                                                $   123,456.78
Item                                                         $           1.00
Item                                                                      2.00
Item                                                                      3.00
New Balance                                                                       $   129,456.78

There are over 100 pages. Some with 1, 2 or 50 items. Some with just the Balance Brought Forward, NO items and New Balance. I need to convert it Excel. Any yes, I know it will not be easy or cheap.
0
I have a simple worksheet (attached). I want to display the Page # of ## in cell F5, where # is the current page number and ## is the total number of pages.
ABC-Company----TB---1705-Rev7EE.xlsx
0
For example if I had www.mywebsite.com/somepath/my-excel-file.xlsx

Can I use this URL on the data source for the ODBC connection?

How can this be done?
0
I'm trying to use the date as a filter on an OData feed in Power Query Excel. However, I still get all rows from the data set. I don't want to use filters in power query to narrow my data set because this pulls the entire table data and it is too large. I want to use the OData Feed URL source.

Here is what I have. I imagine it is just syntax.
= OData.Feed("https://test-uat.sandbox.ax.dynamics.com/data/Transactions?filter=ProjectDate gt datetime'2017-08-08T00:00:00'")
2017-08-22-11_08_09-Query1---Query-E.png
0
I enable filter in my spreadsheet.  For some reason, when I go to a column and select specific fields to filter, it just show everything.  

I have tried unfilter and filter again but the result is the same.  

Please assist.  I do not mind to reset the filter.  

Thanks.
0
Hi everyone. How do I model this in Excel? It isn't a homework question, but a real life example of why they made you do these types of problem in math class.

I am borrowing $30000 to buy a car. There is an option available to get 0% financing, but only for 24 months, which would make a monthly payment of $1250. I also have access to a line of credit which has an rate of 3.5%. Currently I have $500 per month I can pay from my account. What would total cost of borrowing be if I took the 0% loan, paid $500 a month from my account and the remaining $750 from the line of credit. Then after 24 months, I would then use the $500/mo from my account to pay back the line of credit.

I have attached a spreadsheet showing the cost of just financing the whole thing on LoC vs the cost of the above in two stages. Just wanted to be sure that it is correct.
Loan-Calculations.xlsx
0
I have an Excel sheet that hides rows depending on whether the user is or isn't a member of a particular group.
Recently, the code has just stopped working. Without any adjustment to the actual code (I know this because I password protected it and 100% certain I have not amended it)

The Macro errors out on line Set grp = GetObject(strPath & strGroup & ",group")

Full code below...

Function IsUserMemberOf(strDomain As String, strGroup _
  As String, strMember As String) As Boolean
  Dim grp As Object
  Dim strPath As String

  strPath = "WinNT://" & strDomain & "/"
  Set grp = GetObject(strPath & strGroup & ",group")
  IsUserMemberOf = grp.IsMember(strPath & strMember)
  Set grp = Nothing
End Function

Function GetCurrentUser() As String
    GetCurrentUser = Environ("USERNAME")
End Function

Function GetCurrentDomain() As String
    GetCurrentDomain = Environ("USERDOMAIN")
End Function

Open in new window


and the line of code that calls the IsUserMemberOf function...
If IsUserMemberOf(GetCurrentDomain, "GroupName", GetCurrentUser) Then blnUserPermittedSheetAccess = True Else blnUserPermittedSheetAccess = False

Open in new window


What could be wrong?
0
i work by ecell i want when write in Specific cell
 so move  my information in other sheet.
i think say coding
i wish to explain my means.
0
[Webinar] Lessons on Recovering from Petya
LVL 9
[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Hi

We have couple of Excel spreadsheets which we are struggling to open. Both of them take long to open in Excel 2013 and Excel 2016 does not open it at all (or we just do not have patience waiting after it has not been responding for so long).

1st spreadsheet has few worksheets of which last three have only cells contain text (i.e. names and addresses) and the first worksheets use the following/similar formulas to pull data out of the last three worksheets:
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$C$220,2,0)
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$E$220,3,0)
=VLOOKUP(C80,'Tenants and Property Addresses'!$A$2:$E$220,3,0)
The thing is that each worksheet which contains =VLOOKUP formulas has about 25 of them populated and other 25-20 which have the =VLOOKUP formula but are not populated since we did not use it yet and then it displays the errors because it does not contain data.

Is there a way to have the formulas in but not act until the data is entered?
Is this the right formula we are using?
Should VLOOKUP be that slow? or is there a way to make it better/faster?

2nd spreadsheet is just a typical bank reconciliation document. It has got months with transactions and then one which sums it all up. The monthly worksheets only have basic SUM formulas and numbers data, the summing up one is pulling some Totals from the monthly worksheets. I checked and the formulas and formatting are only in the cells which are being used. But I noticed …
0
Hello, I was wondering if it's possible that I could set up a customer pivot table to repeat these four things for each customer?  

Example:
Customer A
Customer B

Customer A  | Jumbo
Customer A  | Large
Customer A  | Medium
Customer A  | Small

Customer B  | Jumbo
Customer B  | Large
Customer B  | Medium
Customer B  | Small

Thanks
0
I have a query that I would like to add a column that says Yes or No to
The existing query works fine in retrieving the data to excel
But then I have to insert a Yes/No column in the spreadsheet, can I get the query to put this in?
0
Hi,

I'm stuck with an issue on one of our end-user computers.
There is an xlsm document containing important info which one particular user cannot save on her brand new computer.
Other documents (including xlsm) can be saved without issues.

Specs:
Windows 10 1703
Office 365 ProPlus 2016

Error:
Errors were detect while saving ...xlsm. Microsoft Excel may be able to sae the file by removing or repairing some features. To make the repairs in a new file, click Continue. To cancel saving the file, click Cancel.

Error message when saving
Clicking Continue will corrupt the file and render it useless.
Clicking Cancel will produce the warning "Document not saved".

Tried:
The exact same document can be saved on other computers.
Enabled all Macros, turned off protection mode.
Document is located on a network share: Copied file locally, same issue.
Added location to trusted locations.
Turned off Antivirus and Firewall.
Opened document with "Open & Repair" -> Same issue
Updated Office to the First Release Channel. (was Deferred Channel)
Uninstalled Office and Installed from scratch.

Any suggestions?
0
HI

I have a field like 01MAY17:00:00:00 that I want to convert to a date format yyyy-mm-dd. I am able to manually trim the above to "01MAY17" and then apply the datevalue function to it. The problem is I can not manually trim each and every column cell to remove the time string.

Any suggestions to go from "DDMMMYY:00:00:00" to datevalue yyyy-mm-dd (the target is got by removing the time portion and then applying datevalue function to it)?

This is excel 2010.

Thanks
0
1) I was asked by my colleagues if when we add a new line, that it didn't go to the bottom, can it be coded to be alphabetized by the Employee name in the list(last name, first name).

2) I was asked if we could search and go right to a particular record in the form vs. scrolling. For example, I want to update only Sample 3. How can I bring that record up in the form? Can we put that name up, or some other unique ID like a phone number, that only the user would know?
0
Hi Experts,

I have an event that loads data from an Excel File into a Table.  The process works perfectly with the data from the first column of the Excel File written into the first column of the Table.

I would like to change this to load the first column of the Excel File into Column 10 of the Table.

Is there a way to do this?  i.e. Specify the Starting Column for the data to be inserted in the Table?

I am attaching the section of VBA code that currently does this.

Thanks,
Bob C.
Import-Excel-Data-To-Access-Table.txt
0
Hello my peers,

I need your guidance and help please to amend the below code that I to include in additional filter requirements and to loop through all files in a folder and complete the below code to every workbook.

Code that i have tried amending is: ws.Cells(1, icol) = "SEL"

I need to add  "STORE" & "CATEGORY"

and finally to loop through all files in folder and complete the below code.

SO END RESULT WOULD BE:

Data split into new worksheet with column data for  "SEL" & "STORE" & "CATEGORY" and finally to loop through all files in folder and complete the below code.




Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 4
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:L1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "SEL"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then

Open in new window

0
I am at long last trying to learn VBA, and have a Kindle version of Excel VBA Programming for Dummies by John Walkenbach. I have followed exactly the instructions he gives for creating the simplest of macros to create my name and the date, in bold text and 16pt. I followed the instructions EXACTLY, and do not get the expected result. I attach my spreadsheet with this Question, and also a screenshot of the Visual Basic Editor shown in the Dummies book.
What have I done wrong? I want to be able to go through this book and learn VBA, but with this bad start I am already foxed.
Any ideas?Macro-Lesson-No.-1.xlsmThe 'Dummies' VB Editor page screenshot
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Importing data into programs via Excel sheets. Sheets contain many columns, need to create "specs" in other programs referencing the row letters above column headings in order to match data with import.  How can I easily get the letters (or numbers) above my column headers to fill down into the first row of my sheet?  That way I could copy the two top rows that contain my headers and the letters above, and then paste and transpose, so I can use as a reference guide when completing the match up during the import process.
0
Hi,

I can able to open chrome in vba but not able to click a button.

Could some please help me on this?

Button ID: tab-1195-btnInnerEl

Sub click()

'Set objgc = CreateObject("ChromeTab.ChromeFrame")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & " -url https://connect.ihs.com/home")
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & " -url https://autoinsight.ihs.com/")
Application.Wait (Now + TimeValue("00:00:15"))
'objgc.Document.getelementbyid("tab-1195-btnInnerEl").click

End Sub
1
Hey EE, I'm currently a little over my head in my a project for my work, what I'm trying to do is make an excel spreadsheet where after inputting how many of each product we need to make to reduce excess prep and wastage, for example 200 Ham Salad Sandwiches, the spreadsheet would in turn say how much of each ingredient would need to be prepped, each of our products have a set portion based off grams, then work out how much of that ingredient would fit into our Prep Tubs and how many tubs we would require each day, I've only recently started using Excel after about 10 years so my knowledge is spotty at best, any assistance would be appreciated.
0
I had this question after viewing Find 10 closest x,y coordinates between two huge x,y data set.


I have an array of XY points corresponding to XY location on a grid.   There may be >100,000 entries.
I  want to compare the distance defined by Sqrt((X-Xa)^2 + (Y-Ya)^2) for a given point to every other point (row ) in the array and count the number with a distance less than some value I can define.
The output would just have the counts added to the XY data into another column for each row.
I'd need to do in excel.
0
NEED TO DELETE ROWS A-O in every csv in a directory , need a vba code to do this
0
I have 80 records with say columns 1-8 in worksheet 1. In worksheet 2, I have copied (linked) say columns 1-3 into worksheet 2. In worksheet 2, I then add columns  4-16 repenting the months Jan-Dec.

Now everything is OK until somebody sorts worksheet 1. Within worksheet 1 we use a value to assess priority and sanction projects. It is nice to sort ascending and we graph this. However, within worksheet 2 (when filter applied) the 80 records are adjusted for columns 1-3 but not for 4-16. I am surprised at this, but when I thought about it you are only linking back to data and not associating with the row in worksheet 2. Am I using the right approach - linking. I do not want to turn of filtering etc in worksheet 1.  Thanks in advance Paul.
0

Microsoft Excel

131K

Solutions

37K

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.