SpreadEveryone: The Fetchland Excel wire
97 watchers
Oct 2021
5:30pm, 7 Oct 2021
5,250 posts
|
um
I'd use vlookup ... but I'd be castigated for that here ! Or if you just want to look, open both windows at once and view side by side. With sync'd or independent scrolling as desired. |
Oct 2021
6:24pm, 7 Oct 2021
4,854 posts
|
run free
Set up some relationships with the tables. See the Data Relationships You can then do a side by side using PivotTables Or do as Um says |
Oct 2021
6:32pm, 7 Oct 2021
15,674 posts
|
larkim
I want to be able to distribute it so window viewing isn't really an option. And a lookup wouldn't exactly work as there will be records in set A that don't have a corresponding record in set B, but I need both of those records to display. If this was in Access I could easily create a report to do it but I suppose that's not different to creating a single big table with all of the data in it and a helper column to identify which is data set A and which is data set B. I can do that and get a workable pivot table, but it's not quite what I want visually. |
Oct 2021
6:40pm, 7 Oct 2021
5,251 posts
|
um
Assuming employee id is unique and 'complete', I'd merge & sort on that in a new sheet, then use vlookup (with blanks if not found as opposed to errors, or tagging to how whic data sets it's in and only look up if there) to display the data? It means it reports all on a new sheet, but is that the same as creating a new merged set of data? |
Oct 2021
9:22pm, 7 Oct 2021
15,675 posts
|
larkim
Yep, that sort of workflow is ok. Was just hoping there was a new thing in Excel as I've not caught up lots of the new stuff like slicers and other data tools so wondered if something in there might work.
|
Oct 2021
10:10pm, 7 Oct 2021
4,855 posts
|
run free
Larkin look at using power query. Should give you what you want just like access
|
Oct 2021
10:12pm, 7 Oct 2021
4,856 posts
|
run free
It’s in your Data tab think it is Transform & Query group. Or Query. Sorry don’t have it in front of me
|
Oct 2021
6:48am, 8 Oct 2021
15,676 posts
|
larkim
Thanks, will take a look!
|
Oct 2021
8:45am, 26 Oct 2021
15,874 posts
|
larkim
If I've got a field in a table which is ABCDxxxxxxx and I want to use ABCD as the row heading in a pivot table, is that possible with a calculated field? I know I can add additional columns to my table to do is as =left(A1,4) etc but calculated fields in pivot tables mean I'm not as vulnerable to numbers of rows changing etc when I acquire updated data so I use calc'd fields for some of the data calculations, but there appears to be a restriction on rows being used in that way. Would be a neat and tidy way of providing an additional level of summarisation (ABCD in this context is a division prefix).
|
Oct 2021
8:56am, 26 Oct 2021
5,321 posts
|
um
Have you tried the analyze/active field options - any way to add a calculation in there? extendoffice.com In the past I've done as you suggest, adding an extra column. Sometimes if it was eg a monthly report, so I was doing it regularly, I used a recorded macro, and a few extra conditions to test the line was populated before adding a load of errors or other into the mix. |
Related Threads
- Excel skills test Mar 2019
- Accounting Question Oct 2017
- Important excel files deleted - Please Help Oct 2016
- download training log to excel Jan 2024
- Excel boffins - your needed! Sep 2018
- World Cup sweepstake in excel May 2018
- HELP! Excel file corrupted and I need it! Aug 2016
- Running Club Spreadsheet to track PBs Apr 2014
- The Retirement Thread Mar 2024
- What do/would you miss about your job? Feb 2024