SpreadEveryone: The Fetchland Excel wire

97 watchers
um
Oct 2021
5:30pm, 7 Oct 2021
5,250 posts
  •  
  • 0
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
  •  
  • 0
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
  •  
  • 0
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.
um
Oct 2021
6:40pm, 7 Oct 2021
5,251 posts
  •  
  • 0
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
  •  
  • 0
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
  •  
  • 0
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
  •  
  • 0
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
  •  
  • 0
larkim
Thanks, will take a look!
Oct 2021
8:45am, 26 Oct 2021
15,874 posts
  •  
  • 0
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).
um
Oct 2021
8:56am, 26 Oct 2021
5,321 posts
  •  
  • 0
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.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work









Back To Top
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 112,115 Fetchies!
Already a Fetchie? Sign in here