David from Florida has two workbooks that he wants to combine.
Both workbooks have the same field in column A, but then different data in the remaining columns.
One workbook might have extra items that are not in the other and David wants to see those.
There are no duplicates in either file
You can use Power Query to solve this.
In David's case, each data set is in a separate workbook.
Start in a new blank workbook on a blank worksheet.
Step 1: Data, From File, Workbook. Close & Load to… Only Create Connection
Step 2: Data, from File, Workbook. Close & Load to… Only Create Connection
Step 3: Data, Get Data, Merge. Select the two connections. Select the column common in both.
For the join type: All from larger file, matching from smaller...
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Filters
Show only:
Loading…
Why does the Comma icon in Excel do so much more than a comma? 75% of the things that it does are not advertised, not expected, and not wanted:
1. Adds two decimal places
2. Shows zero as hyphen
3. Moves the numbers away from the right edge of the cell
4. Adds a comma
Greg in Fort Wayne suggests using Cell Styles Comma 0
& this does remove one annoyance for a few extra clicks
Dustin suggests some code for your Personal Macro Workbook
Sub FixCommaStyle()
ActiveWorkbook.Styles("Comma [0]").NumberFormat = "#,##0"
End Sub
Vote:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/33545875-number-format-thousand-separator-and-no-decimals
To download this workbook...
Bill's problem today: I recorded code to insert a picture, and it is creating a link to the picture
Don't use the recorded code of ActiveSheet.Pictures.Insert
Instead use updated code of
ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
To download this workbook: https://www.mrexcel.com/download-center/2018/06/vba-insert-picture-bug.xlsm
List of upcoming seminars: Excel Seminar Schedule
You want VBA to write several copies of the current Excel workbook. SaveAs causes problems, because the original workbook is no longer open
Instead use .SaveAsCopy to save a copy of the workbook
As far as I can tell, SaveAsCopy does not exist in the Excel interface.
It allows you to keep the current workbook open with the original name and path, but save extra copies with a new name.
This is particularly handy if you need to remove macros from a workbook while saving.
Download the code from the video at the link below.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/save-as-keeping-original-open.xlsm
List of upcoming seminars: Excel Seminar Schedule
Sam in Nashville is filtering a pivot table
If you use Date Filters or Label Filters, Excel remembers the setting and will re-apply the filter after a Refresh
But… if you use the Search box to filter, Excel will not re-apply the search at a Refresh
Instead, use the Label Filter for "Contains"
To download this workbook: https://www.mrexcel.com/download-center/2018/05/pivot-table-search-bug.xlsx
List of upcoming seminars: Excel Seminar Schedule
Why does our employee May keep sorting to the top of the pivot table?
By default, pivot tables will follow the sort pattern of any custom lists.
If you employ people named June or May or Wednesday, they will sort to the top of the list.
File, Options, Advanced. Scroll all the way down. Edit Custom Lists.
The names in the first four series can not be removed.
I used to joke that one solution is to fire all the people named April or June.
Right-click the pivot table and choose Options.
Go to the Totals & Filters tab.
Uncheck Use Custom Lists when sorting.
Download the file to work along: https://www.mrexcel.com/download-center/2018/05/pivot-table-no-custom-sort.xlsx
Read the article: Pivot Table No Custom Sort
A company wants to bill in quarter hours and they always want to round up to the next quarter hour.
1:42 There are 24 hours in a day. There are 24x4 or 96 quarter hours in a day. Thus, 15 minutes is equal to 1/96
2:07 Use CEILING(A2,1/96) to round up to the next higher quarter hour.
2:45 There are 24 hours in a day. There are 24x12 or 288 five-minute periods in a day. Use 1/288 to round to nearest five minutes
3:02 To round to the nearest six minutes, use 1/240
3:10 Use 1/1440 to round to nearest minute
3:33 Use FLOOR(A2,1/96) to round to the lower quarter hour.
3:47 Use MROUND(A2,1/96) to round to the nearest quarter hour.
5:26 Why a column of times do not sum correctly
6:28 Bonus tip: Which number format? 13:30 or 13:30:55 or 37:30:55...
Question from the Gulf South Council of the IMA seminar in Atlanta
Hey! I have data formatted with every other row in Grey
When I insert two rows, both rows are grey.
I have a great solution with conditional formatting, but it requires 3rd grade math
Divide the row number by 2. What is the remainder? It is going to be 0 or 1.
Set up conditional formatting to check to see if =MOD(ROW(),2)=1. If it is, fill that row.
It works awesomely.
Feel free to download the workbook and set up a MOD function to highlight 3 rows in orange and one row in Teal
It is all in the MOD function.
To download this workbook: https://www.mrexcel.com/download-center/2018/05/inserting-alternating-rows.xlsx
Flo from Nashville: Can I VLOOKUP to two different tables?
Look for the item in catalog 1. If it found, then great.
But if it is not found, then move on and do a VLOOKUP from Catalog 2.
My solution: Start with =VLOOKUP(A4,Frontlist,2,False). Wrap that VLOOKUP
in the IFERROR function: =IFERROR(VLOOKUP(A4,Frontlist,2,FALSE),VLOOKUP(A4,Backlist,2,FALSE))
The article for this topic is at VLOOKUP To Two Tables You can find the link to download the file from today's video near the end of the same article.
