Microsoft Excel

137K

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

VBA: Compare two separate excel sheets column and delete unique value Rows from sheet #2 only.(Not two sheet)
Previously, I already got a perfect VBA to delete unique lows from two sheet, so Sheet#1 and sheet#2 have identical data.

Now, I need to compare two sheet and delete non matched rows from only 2nd sheet. The result is sheet #1 data does not change, sheet #2 have same or less rows.
Sheet #1 data have only 1 column in A.
Sheet #2 data have 6 columns and need to compare with B column.

Please ask any question.
Thank you.
0
In my Excel 2016 I have Power query based on Store procedure with two parameters running on SQL Server database as source.
 On top of my worksheet where I have result I have a table named "param" with two fields. Here I input my parameters that I  am passing to sp.
Table  "param"  has two fields:  ExchangeRate and CheckDate.

I am getting this error in data model .
"
Expression.SyntaxError: Invalid identifier."
Error point to part of code [Name="param"] not sure why this is happening.

Here is code part:

let
    ExchangeRate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”ExchangeRate”],
                                                                                  ^^^^^
    CheckDate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”CheckDate”],
    Source = Sql.Database("MyServerName", "MyDatawarehouse",
    [Query=”exec [MyDatawarehouse].[reporting].[My_StoreProc] ‘”
    & Number.ToText(ExchangeRate)
    & “‘, ‘”
    & CheckDate
    & “‘”])
in

      Source
0
My background is as a computer programmer specifically using access databases and VB for apps. My new job requires me to work with spreadsheets in excel. I understand the fundamentals in regards to creating tables from a master worksheet in excel and manipulating the information, vlookups, etc. Part of my job will be updating and adding information onto the master spreadsheet from various tables created by various department. I have not worked with the master spreadsheet yet but understand that there are various macros and formulas embedded in the worksheet. My concern is that I know when adding or updating information via rows or cells that this can have an effect on the entire worksheet. I am maticulas and do not want any mistakes. How would you recommend the checking process prior to entering on a production worksheet? I understand checking for duplicates on the table, checking formating in table cells to match the master sheet, etc. The master sheet has a large volume of data and I don't want to make a mistake by updating one cell and affecting another. Thanks for taking the time to review and offering suggestions on a checklist to adhere to accuracy.
0
Hello experts,

I have the following attachedxml file which allows me to select specific tables through ADODB.Connection
Here is the procedure:
Sub SQLQueryOut2(wsName As String, strSQL, strInitialRange, strServer, strPort)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


' ==> Connections
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={PostgreSQL ANSI};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Port=" & strPort & ";" & _
"Database=db;" & _
"sslmode=require"

SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Sub SQLQueryoutConfigSheet()

    Dim wsConfig As Worksheet, 

Open in new window

0
I have a list of URL's that I copied that have the Text and the URL.  I know how to get rid of the URL and just have Text but I want the opposite. For instance, if my copied URL is:

Google   <----- This is the link in Excel. Under it is https://www.google.com

I want to extract the URL from the Text so the result is:

http://www.google.com

Is there a formula to do this?
0
I have multiple timesheet tabs for each week until the end of the year.  Each worksheet the user will select a project number and put down hours against that project number.

I populated two tabs Time 10-21 and Time 10-28 with some project numbers and hours.  What I'm asking is on the "hours Tab" that the hours are being added from  each tab.  See hours on the "Hours Tab" for examples.

See attachment
C--Users-lfreund-Desktop-TTE-T-M-20.xlsm
0
Hi,


Is there way in Excel to find duplicate values? I don't want to delete but list duplicated value from a cloumn.
0
Excel 365 crashes when I try to find using "ctrl F"
0
VBA: Compare two separate excel sheets column and delete unique column value rows from 2 sheets.

For example,(compare column B)

1st sheet     2nd sheet
1 2 3 4 5     1 2 6 7 8
2 3 4 5 6     4 4 5 8 9
3 4 5 6 7     2 5 7 8 9
                     3 8 9 0 1
After
1st sheet     2nd sheet
1 2 3 4 5     1 2 6 7 8
3 4 5 6 7     4 4 5 8 9


Thank you~
0
I have a report

Start.PNG
that I need to snap every day, and then stack them into a table

End.PNG  

in order to convert it to SQL Compliant blocks.

I know how to write the vba to do this (with a lot of "Finds" and Range(XLDown), etc), but I was wondering/hoping if there a shortcut formula or just a cleaner Excel technique to do this.
0
An excel macro workbook, which worked fine for many months, all of a sudden, started balking at this code:
Worksheets("Menu").Activate

It is in Sub Auto_Open(), because I want the "Menu" sheet to activate when the workbook is opened.

Now, when it is opened, the following error appears:
Run-time error '57121':
Application-defined or object-defined error


Even when I comment that line out, and then run it in the immediate window, I get the same error.

I don't know if this is related, but at the same time, I started getting an error when using a calendar widget on the same sheet. But I removed the calendar and this error still appears.

Any ideas? I'm perplexed.
0
Text file to excel.
I have two set of txt file and I can import to a sheet of excel.(sheet name: R, S) But I want to import to one sheet using VBA.
Also, I want to delete duplicate value(name column) from each set of data(ex, lee from sheet R, kim and lee from sheet S)
Is it possible ?

Thank you.
0
Hi

I have a workbook and  what im trying to do is at every change in number in column AZ I need to insert a  blank row.

Any ideas?
0
I have an older version of Crystal Reports (2008) on an Win 7 Pro machine.  However the data it is working with is being compiled in Excel 365. Which upon opening will not allow a connection in Crystal Reports 2008. Is there an available connector I can install or download for the ODBC admin so that it shows up and allows CR to read a newer version of an excel spreadsheet? I realize I could have the file saved in an older version of Excel however I have wondered if there was a newer driver that would work with CR that could be installed so that I do not have to keep doing that. Does Excel 365 have a driver that would work with Crystal Reports that can be added to the ODBC administrator tool?
0
If I have custom formats that says surplus if it's greater than 1 and deficit if it's less than or equal to one, can I use conditional formatting to show all the surplus as green and the deficits as red
0
Hi Experts,,
I have a MS Office 2016 Access application that includes code to import data from an Excel Spreadsheet.

For Users that don't have MS Access they can download and install MS Access 2016 Runtime and the application itself works perfectly.

However when they try to use the Import From Excel function they get an Error if they don't have Excel installed.

How can I / they get a Runtime Version of Excel installed so that they can use this function?

Thanks,
Bob C.
0
Hi,
I have multiple spreadsheets open at the same time sometimes 4-5.
Can anyone advise best specs for monitors for this purpose.
giving clearest crisp text.
Will 4K improve text quality ?
I will use a 3 monitor setup and will NOT use for gaming.
Everything on the net nowadays relates to gaming !!
Thanks
Ian
0
Hi

I have 2 sets of data in columns A-E , In column A it will say 1 or 2 thus notifying it set 1 or 2 of data and then the data.

What im trying to do is look all the way down the sheet comparing data in each row in columns A-E and if data  in column "A" but not in row marked  2 I need it marked in column F of  row concerned  "MISSING FROM ROW 2"

I then need it to run all the way through the worksheet the other way round where as if in column A there in no data in row marked  1  but there is data marked 2 in need the row marked "MISSING FROM ROW 1"

Finally I need it to compare all data that starts with no2 in column A and if duplicated  I need one of the  entries marked "DUPLICATE"

If same data in rows marked in column A 1 and 2 then I dont need anything doing.
0
Hello experts,

I have the following procedure which allows me to generate random values.
Sub Generate_Random_Values()

Dim wb As Workbook
Dim wsRandom As Worksheet
Dim celAddress As String
Dim typeRandom As Long
Dim rngInput As Range
Dim FormulaString As String
Dim shName As String
Dim col As Long

    Application.ScreenUpdating = False
    On Error GoTo Error_Routine

Set wb = ActiveWorkbook

celAddress = InputBox("Enter last range in which you want to apply the procedure.", "Last Cell In The Range!", "V1000")

If celAddress = "" Then
    MsgBox "You didn't enter the last range.", vbExclamation
    Exit Sub
End If

On Error Resume Next
Set rngInput = Range(celAddress)
col = rngInput.Column
On Error GoTo 0

If rngInput Is Nothing Then
    MsgBox "The range you entered is an invalid range.", vbExclamation
    Exit Sub
End If

typeRandom = Application.InputBox("Please input..." & vbNewLine & vbNewLine & _
                    "Enter 1 for Random Numbers." & vbNewLine & _
                    "Enter 2 for Random Letters." & vbNewLine & _
                    "Enter 3 for Random Letter and Numbers.", "What Type Of Random Values?", Type:=1)

If typeRandom = 0 Then
    MsgBox "You din't enter the type of Random Values.", vbExclamation
    Exit Sub
ElseIf typeRandom > 3 Then
    MsgBox "You entered an invalid random type.", vbExclamation
    Exit Sub
End If

Select Case typeRandom
    Case 1
        shName = "Numeric_" & Format(Now, "YYYYMMDD_HHMMSS")
        FormulaString = 

Open in new window

0
How do you identify the year in a column in excel and build an If Then Else statement to change the year in that column?
So if the excel file included has a date field with a time stamp in it. How do I find the year and then determine if the year is say 2004 then switch it to 2018 else if 2003 then 2017 etc.

I found this part of the equation in a youtube video but I cannot seem to google the right words for finding the year for the if then else statement? Any thoughts are much appreciated. I got parts of it but keep failing at the correct sequence. If written in the English language it would look like this:
If YEAR(DateTime) = "2004" then Replace YEAR with  "2018" else if ....Orders.xlsx
0
Hi Experts,

I am using SQL Server 2008 and I have exported a table query and updated one of the columns using Excel.

What is the easiest way for me to import the updated values into the same table?

Thanks!!
0
I have a spreadsheet with 4 tabs and about 60000 rows in each tab, in one of the tabs i have all the formulas like index match, sumif and countif, the issue is for every calculation it is taking hours and excel get stopped how do I do this calculation faster?
0
Hi all!

I am trying to place the items measured as header to left side of the graph and the dates header on top.  When highlighting columns/row, it places dates right side and the items at trhe bottom (see pic below):

GrapthHeaderLocationACTUAL
However, when highlighting just a few, it places the items to the left side and the dates to the bottom (see below):

GrapthHeaderLocationDESIRED
Below i what I am trying to do (items right side, dates on top and scale to the right) as below:

GrapthHeaderLocationDESIRED
How can I do this?
0
Hello,
      I have an Excel file that I would like to combine columns A and B and add a dash between the two. I have tried using all the “tricks” I know but cannot seem to pull it off. For example, column A is 15544 and column B is 21. I would like this to become 15544-21. Is this possible? I have attached a file as an example.

Thank you
Example.xlsx
0
Hello,

I am working on a spreadsheet which I want to compare column C to column A.

For any item in column C that appears in column A, copy the logon date next to column A to column D next to that matched item.  

Please see that attached.

Is this possible?

Thanks.
test-compare1.xlsx
0