Hi, I am trying to get my head rou

[cneill]

Hi,
I am trying to get my head round INDEX MATCH for the first time but am not doing very well

Could someone help me with this please

I tried to use =INDEX('Master List'!E5:E5:E5020,MATCH(E5,IF('Master List'!AQ5:AQ5020=C3, 'Artwork and Sculpture'!C3:C150),0))
But the result is just "value"

The column in the sheet 'Master List'!E5:E5020 has the name of the person I am trying to look up

The column in the sheet'Master List'!AQ5:AG5020 has records with a Unique ID Number

The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number

I want to look up the name of the person from the Master List and add the name to the cell on the 'Artwork and Sculpture' sheet next to the "C" Column in the "B" Column

Example: Franz Joseph Hayden's name is in the column in the sheet 'Master List'!E5:E5020
The column in the sheet 'Master List'!AQ5:AG5020 has records with a Unique ID Number in this case the Unique ID Number is "1"
The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number of "1"
So 1 needs to match to 1 and Franz Joseph Hayden to be the result in the sheet 'Artwork and Sculpture' in cell B3

Any help would be great appreciated

 

[cneill]

Hi Skip,

If your happy to do that for me, that would be wonderful, I will send you a new file with some additional data.

Talk soon

 

[SkipVought]

I've got an afternoon appointment in an hour. I might not get to this today.

But, Yes, I'd be happy to do what I can do.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Is this the best way to do this?

Based on the exchange here with Skip, looks like it is doable in Excel, but...
Wouldn't be better to do all of that in the data base? Access maybe

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[cneill]

Hi Skip,

Attached is the updated sample file, with some more data, let me know if that is enough data.

 

[cneill]

Hi Andrzejek,

Thanks for looking at the thread
I think Access would be better, but the society I am helping (Volunteering) don't have a licence for Access and they said they wanted it in Excel.
But a very good thought.

 

[Andrzejek]

You do not need to have Access installed in order to use it. You just need Microsoft Access Runtime

More about it in thread181-1829586

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

@Andy, from what I understand, the problem is not database specific but rather being able to display the selected data on the Profile summary sheet in such a way that is conducive with the data displayed above it.

The single occurrence per ID data at the top of the sheet is displayed with Column Widths that are significantly smaller than the Column Widths of the multiple rows of tabular data returned for any specific ID. So, IMNSHO, it's a DISPLAY challenge of representing heading data and detail data in a pleasing format all on one summary sheet, that is unless the OP has a different opinion than I.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi Skip,
Sorted out the Index/Match date problem with Blank Cells, works for all Pre and post 1900 Dates
so this =INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0))
Becomes this
=IF(INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0))="","",INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0)))
Such a simple solution
Thanks for all your help

 

[combo]

You can simplify and change technology for filtering required data. I enclosed a workbook with some modifications. It has 'changed' sheet with description of changes.
In general, I used:
- names, to simplify references,
- INDIRECT with table column references in data validation formulas,
- power query queries to filter artwork by master (power queries available since Excel 2016),
- event driven macro to update queries when master changes (it needs a trigger, the workbook was in macro-enabled format, so I added one).

So, when you enable macros and connections (to internal data in fact, power query works this way), a selection of master fires event procedure and updates queries.
Take care of table sizes, even with format continuation, one of tables did not contain full data.
Queries can overwrite data below.

combo

 

[cneill]

Hi combo,

Thank you
There is a lot to take in with all these changes (some are new to me)
So I will make the changes to my Excel work book one by one, then If/when I will have any questions I will get back to you
I appreciate all your help

 

[cneill]

Hi combo,

How do I change the "Full Profile" to show Table Design and Query tabs
I tried Insert - Table - Table but it keeps creating Headers which I can't delete

Thanks

 

[combo]

If you mean the three tables - under 'Artwork and Sculpture 2' - they are three separate query results (cols: A, C and F). You can go Data>Show queries (can depend on Excel version). From the ribbon or queries side bar after right-click, you can edit the query in power query desktop.
In fact that are query results in tables.

combo

 

[cneill]

Hi combo,

Sorry
Just getting my head round this
On the Full Profile sheet
If I clink in cell E2 the "Table Design" option pops up on the ribbon at the top
Then when I click on the "Table Design" option I can see the new table name tMasterSel
I don't have this option on my Full Workbook yet, so I need to add this, how do I do it?
I tried Insert - Table - Table but it keeps creating Headers which I can't delete

I hope this makes sense now

 

[combo]

Yes, I added one row and one column table. It is because in Excel the interaction between workbook and power query environment is via tables.

So I need an Excel table with data, next a PQ query imports this table to PQ environment. The 'tArtwork' table is imported in the same way. The new query with inner join extracts data for selected master and returns only single column (there are three copies for three required columns). Each query returns data to table in worksheet.


combo

 

[cneill]

Hi Combo,

Thank you
Can I ask for some hand holding please, I lot of what you have done is new to me and I have only just upgraded from Office 2010 to 2021 so there is a lot of new stuff that I am still learning about.
Re: Full Profile sheet
You Said: I added one row and one column table
Can you explain how I do this, so I can replicate what you have done in the Master Workbook?
Many Thanks

 

[combo]

Select cell E2. Insert table, confirm that your table has header. Excel adds structured table; from the table menu you can change name and table layout. I choose top-left option from the gallery - no formatting, so your original formats were not changed (

https://support.microsoft.com/en-us...-excel-bf0ce08b-d012-42ec-8ecf-a2259c9faf3f).

A new feature in Excel (since Excel 2016) is a Power Query com addin, for data transformation. If a data source is in excel workbook, it has to be a structured table. When you select a cell inside such table and add a query, you will land in PQ desktop (

https://support.microsoft.com/en-us...-query-ca69e0f0-3db1-4493-900c-6279bef08df4).

A query in PQ is a series of transformations, with filtering, unpivoting, merging tables, creating custom columns, deleting columns, functions, etc. The result can be returned to worksheet (in table) or stay for future processing. The steps in PQ query are recorded, so it is easy to start for the beginner. In advanced view one can see the code behind the query - M language was created for the use in PQ. M offers far more power than basic recorded and modified actions. It can access many different data sources, as Access, SQL Server, file system, xml, pdf tables, and many other.

MS in the second link mentioned the Data Model (I haven't used it in in the uploaded workbook). It is possible to create relations between tables added to the model, it can be queried, and pivot table using multiple tables in DM can be created. With cube functions aggregations similar to pivot table can be generated (it is possible to convert pivot table to a set of cube functions and leave one cell only, for instance). Another com add-in, Pover Pivot, is used to manage the data model with its own DAX language (I don't know if it is available in all Office versions, I have 2016 Professional and it was here, but not in Standard).

So a lot changed in the meantime. Personally I think that the best new Excel feature is Power Query.

combo

 

[cneill]

Hi Combo,
In my Master Workbook
I made a copy of the Full profile Sheet and renamed the current one as Full profile Old (full back position if I do something wrong)
Clicked on E2 Full profile Sheet and Inserted the table, I confirmed the table was located at $E$2, I confirm my table has a header, choose top-left option from the gallery - no formatting.
Now cell E2 has two dropdown arrows, one as a Header arrow and one for the dropdown list
My updated sample you sent me only has the one for the dropdown list
So why am I getting a header when I confirmed I already had headers?
Many Thanks

 

[combo]

In the table formatting ribbon, untick filters.

combo

 

[cneill]

Hi Combo,

Sorted Thank you
Re Changes
1.) Resize 'tArtwork' table to cover full data - How do I do this?
2.) Master-ID' field in 'tArtwork' table DV formula changed to =INDIRECT("tMaster[Master ID]") - I can't find this change you made, where is it?
Thanks

 

[combo]

1) there is a small blue dot at the right-bottom corner of the structured table. Drag it to resize the table. Alternatively, when active cell is inside the table, the change table size option is available from table design tab in the ribbon.
2) DV - data validation, see formula applied.

If you have never before workeed with structured tables, try it in new workbook. Write a small set of data in tabular format, with text, dates and and numbers. Convert it to table. Experiment with table styles and options. Add/remove totals row. Add new data in row and see how the table resizes automatically. Write formula in adjacent column referring to the same row - see how excel adds new column and fills it with structured references; repeat this step in cell in other column - excel will create external structured reference, with table name, in a single cell.
Try slicers - alternative to filters in tables and pivot tables. Create pivot table - note that data range refers to table name, so no problem with changes in source size.

combo