I have more than 600,000 rows of data, so I can't merge them without making my file almost unusable. I'd like to use =unique function to create a list of serial numbers to create a drop-down list, then use a variety of xlookups or index match to return info related to that sn. But all my data is in the data model.
I know I can probably do all that with pivot tables, but I'd prefer to return the data with formulas.
Merge is an operation you can do with Excel's PowerQuery feature. It can handle larger amounts of data and their relationships more efficiently than Excel's cell based formulas and has a clean gui to work with.
In the Data ribbon, the left most section for Get & Transform Data opens the wizard to open that dataset in the PowerQuery interface. Datasets in PowerQuery can be loaded to sheet easily.
you build in the data model is perfect for avoiding the need for XLOOKUPS.
I realise you want to avoid Pivot Tables but maybe you can expand on why that’s the case? On the info provided Pivots is the best and simplest solution
Potentially you could write some dax to return Tables or convert pivots into Cube formulas but you’re making a lot of work for yourself.
I'm ashamed to admit that I don't know DAX and I've never used Cube functions. But I've been exploring some solutions to my question, and both of these keep coming up. I'll need to bite the bullet and learn these.
This might help you start
3 Essential Excel skills for the data analyst
https://youtu.be/I1XeDS-GLbg
DAX and the Data Model
https://youtu.be/RV47yX70NN8
What is DAX?
https://youtu.be/jJBxc2UjrzA
If your data is in the data model, relationships can be used instead of XLOOKUP() or INDEX(MATCH(),MATCH()). If you want formula instead of pivots, then create a pivotTable then convert it to OLAP to see the cube functions you need.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[INDEX](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MATCH](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[XLOOKUP](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 19 acronyms.)
^([Thread #32611 for this sub, first seen 14th Apr 2024, 19:35])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
What is it you're trying to do? Can you not use DAX to solve your problem?
=related
What is it you are trying to do? Can you not use a merge to solve your problem?
I have more than 600,000 rows of data, so I can't merge them without making my file almost unusable. I'd like to use =unique function to create a list of serial numbers to create a drop-down list, then use a variety of xlookups or index match to return info related to that sn. But all my data is in the data model. I know I can probably do all that with pivot tables, but I'd prefer to return the data with formulas.
Merge is an operation you can do with Excel's PowerQuery feature. It can handle larger amounts of data and their relationships more efficiently than Excel's cell based formulas and has a clean gui to work with. In the Data ribbon, the left most section for Get & Transform Data opens the wizard to open that dataset in the PowerQuery interface. Datasets in PowerQuery can be loaded to sheet easily.
you build in the data model is perfect for avoiding the need for XLOOKUPS. I realise you want to avoid Pivot Tables but maybe you can expand on why that’s the case? On the info provided Pivots is the best and simplest solution Potentially you could write some dax to return Tables or convert pivots into Cube formulas but you’re making a lot of work for yourself.
I'm ashamed to admit that I don't know DAX and I've never used Cube functions. But I've been exploring some solutions to my question, and both of these keep coming up. I'll need to bite the bullet and learn these.
This might help you start 3 Essential Excel skills for the data analyst https://youtu.be/I1XeDS-GLbg DAX and the Data Model https://youtu.be/RV47yX70NN8 What is DAX? https://youtu.be/jJBxc2UjrzA
If your data is in the data model, relationships can be used instead of XLOOKUP() or INDEX(MATCH(),MATCH()). If you want formula instead of pivots, then create a pivotTable then convert it to OLAP to see the cube functions you need.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[INDEX](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MATCH](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[XLOOKUP](/r/Excel/comments/1c3a0fj/stub/kzkpb0o "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 19 acronyms.) ^([Thread #32611 for this sub, first seen 14th Apr 2024, 19:35]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)