Specification Comparison Table from Different Worksheets

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi everyone, I'm trying to compile a specification table for a product that comes with different models, and I stumble upon on one that exactly matches what I have in mind:


1st step: choose the 2 products for comparison at https://www.dpreview.com/products/compare/cameras
2nd step: display the properties of the 2 (or more products) at this result: https://www.dpreview.com/products/c...ucts=canon_ixus115hs&products=canon_ixus500hs


My question is: is it possible to do this in Excel 365? I have the specifications of the different models in different worksheets and what I have in mind is to compare the models on a new worksheet that'll populate the models that I choose.


Sorry if I'm not making any sense? Thanks everyone.
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
There are various ways it could be done.Provide a sample workbook with some dummy data that resembles your real data - 20-30 rows will suffice.
 

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Sample dummy data

There are various ways it could be done.Provide a sample workbook with some dummy data that resembles your real data - 20-30 rows will suffice.

Hi there, here's a workbook that resembles what I'm after in the comparison tab, and the specs of individual models of different brands are in subsequent worksheets, if that make sense?
 

Attachments

  • Camera Comparison.xlsx
    22.5 KB · Views: 9

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
The attached solution uses PowerQuery to process the camera brand data and produce a composite lookup table and a list of models for the drop-down. The comparison table then uses simple LOOKUP formulae to populate the data. The initial version uses tables and an append query, and the second uses named ranges and the =Excel.CurrentWorkbook() function.

If this is something you feel you can work with, let me know, and I can talk you through how to set it up. Adding more makes and models would be relatively painless.
 

Attachments

  • PQ Camera Comparison AliGW.xlsx
    46.6 KB · Views: 10
  • PQ Camera Comparison v2 AliGW.xlsx
    43.1 KB · Views: 7

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Yes!

Hi AliGW, yes! The output is exactly what I'm after! If you don't mind coaching me through it, I'd really appreciate all the help i can have for this. Thanks!
 
Last edited by a moderator:

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Have a look at the second - it maintains the layout in the individual model pages, however that makes it a little more fiddly to maintain. If you can live with the layout in the first option, I'd go with that.
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
I should add that I don't have time to coach you through both options, so you need to make a decision, please. If you want V2, then I shall need to refine it a little first. Please let me know - I won't start until you've made your choice.
 

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi Ali, thank you for the time and effort to help me on this. I prefer V2 as it'll help me with future additions. Thanks!
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
OK - in that case, let me see if I can refine it first to avoid tricky situations when the queries are refreshed.I will post back later today - I hope you are patient, as this won't be quick, sorry.:)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
I'm sorry - I've realised there's a major problem with the solution I offered. I will continue to look at it and see if I can fix it, but for now I must withdraw it, as it is not pulling through the camera models correctly.

Sorry about that.
 

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
No worries. I appreciate your help on this. :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Did you see the post I just made? Sorry, but I've had to withdraw the solutions offered.
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
I believe I have now resolved the issues. I will post a How To ... within a couple of hours. :)
 

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Thanks Ali! Looking forward to it! :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
And here it is!

I suggest you print out and read through the instructions BEFORE attempting anything. PQ is easy once you get the hang of it, but there's a lot here for a novice, and any slight wrong step will mess everything up.

Also, be sure to use THIS version of the workbook (version 3), as this is the one that has been tweaked. If you want to work through the instructions I have provided to practise setting this up for yourself, then start with the workbook that YOU originally attached to the thread.

Let me know how you get on!
 

Attachments

  • PQ Camera Comparison v3 AliGW.xlsx
    48.9 KB · Views: 4
  • PQ for Camera Comparisons User Guide.docx
    260.6 KB · Views: 3

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi Ali, thank you so much for the attachments. It's the first time I heard of Power Query but I'll look at tutorials to see how to work with it. Had a quick glance of the word doc, and will spend next few days digesting the data. Will need your guidance some time next week on this.

Just a quick question for now: I've also tried to tweak some numbers in the individual worksheet (e.g. Canon R5 model) in the v3 file to see if the comparison table will update itself, doesn't seem to do so unless I tweak it in the Composite worksheet. Is there a way to tweak the numbers in the worksheets (canon for eg) to reflect changes in the comparison worksheet, i.e. by-passing hunting down the corresponding cell in the composite worksheet?
 
Last edited:

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Edit Update: I've look closer at the instruction, i think the answer to my query above is on line 11(d), i'd need to click Refresh All on data ribbon to update the queries. :) Also just want to note that under instruction 2, the syntax "i" has been capitalised by Word doc, so future users will need to change it to small caps.

Thank you Ali for your guidance on the steps. They are really clear and helpful. I'll need to figure out how to integrate it to sets of real data in the next few days! PQ is so powerful! Please don't mind me bugging you on the codes when i encounter some issues...
 
Last edited:

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
No problem. Thanks for the typo notice, but this won’t be used again as it’s completely bespoke to you.

As you have now discovered, you should not be changing anything on the composite worksheet. That updates when the queries are refreshed.

Each query can be set to refresh automatically, up to once every minute, if desired. You can add a bit of VBA to do the refreshing whenever there’s a worksheet change event, if you wish. PQ is indeed very powerful - I often refer to it as Pandora’s Box.
 

Polignac

New member
Joined
Apr 9, 2021
Messages
23
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi Ali, I'm trying to retrofit the codes to my existing data, which contains more rows of data than before, i.e. from 25 rows of data to 59 rows, in addition, the split columns under Image (Boosted) which contains 4 rows of data, now I have 11 rows of data located at rows 6 to 16, as compared to original files at Rows 12-15, if that make sense to you?

Which parts of the codes should I change in order to make it work? I'm looking through the codes and none seem to have reference to the new rows until the very end. Thanks :)
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,800
Reaction score
4
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
I can’t visualise what you mean. Can you provide a sample of the layout? Are you talking about the formulae in the last part or something to do with the query?
 
Top