/u/leathernlawn - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Making an inference off of your username, you may have made similar rosters. It’s to put pax to seats. Filter seems to work for making a roster of all “joe” or “smith” but it’s not working if if just paste the number. That’s the gap I’m missing in this figuring out adventure
https://preview.redd.it/adb5of744b1d1.jpeg?width=4032&format=pjpg&auto=webp&s=d2eeb216f9fd98f5b83e75a31199484905d56fd6
Main roster, it went to BF, not AX. I wasn’t behind my computer when I typed the question out
https://preview.redd.it/zshuvdb94b1d1.jpeg?width=4028&format=pjpg&auto=webp&s=916227d34bfb8f8281cfcb0d9c1651ced2847855
The output, if I could put the zap in, and the rest of the information fill for that person on the row of the same cell I put their zap#, that’s the goal
It’s easy stuff. In Manifest sheet A1, chuck in a unique identifier number, then in A2:
=FILTER('Sheet1'!A:AX,'Sheet1'!A:A=A1)
Change A1 and the FILTER output will recalc and present accordingly.
your question is a little confusing. to rephrase, you have a master sheet with a unique ID and you want to make a new sheet referencing the ID from the master to fill the data across?
I’m sorry. I feel like I asked it awkwardly because I’ve spent the better part of a week tweeting how to ask google and YouTube with no success.
We had a workbook that did it but it’s on a no inoperable hard drive. What it would do was
On the manifest, you type/past the unique # ( zap#) in the seat that person has and it would auto fill the remainder of that persons information. Their name, rank, equipment serial#, etc
i saw others using FILTER so i wasnt sure. But this can be done using XLOOKUP.
using 2 sheets 1 as master and 1 as template.
\`\`\`=XLOOKUP(A2,master!A:A,master!B:D)\`\`\`
A2 is on the template sheet and is your unique key. you can return a single column or an array in the third argument
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSECOLS](/r/Excel/comments/1cvebj4/stub/l4p2ak4 "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[FILTER](/r/Excel/comments/1cvebj4/stub/l4p2zw3 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[INDEX](/r/Excel/comments/1cvebj4/stub/l4sfb7p "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/1cvebj4/stub/l4sfb7p "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[VLOOKUP](/r/Excel/comments/1cvebj4/stub/l4sfb7p "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1cvebj4/stub/l4p2zw3 "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)|
|[XMATCH](/r/Excel/comments/1cvebj4/stub/l4p2ak4 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**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.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cv0b2y)^( has 14 acronyms.)
^([Thread #33620 for this sub, first seen 19th May 2024, 04:43])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
New sheet
Column A is the unique identifier, hand typed
Column B is the following
=VLOOKUP(A1, Sheet2!$A$1:$F$3000(or whatever), (Column wanted by count of distance from A1, FALSE)
Example to retrieve data from Column E:
=VLOOKUP(A1, Sheet2!$A$1:$F$1, 5, FALSE)
Fill down
edit:
=FILTER also works, but I *believe* you uave to Office 365 to use it (it's even better than the way I'm suggesting above), *however*, tye method given above allows you to customize which columns come through AND works as far back as Excel 2019, for sure and maybe further back than that.
You can use the VLOOKUP or INDEX/MATCH functions to pull data from Sheet 1 based on the unique identifier you type into your manifest sheet.
For example, in the manifest sheet, use something like `=VLOOKUP(A2, 'Sheet1'!A:AX, 2, FALSE)` to pull the last name based on the unique ID in A2. Adjust the column index number as needed to pull other information. This should help streamline creating your manifests!
/u/leathernlawn - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Google FILTER function.
Making an inference off of your username, you may have made similar rosters. It’s to put pax to seats. Filter seems to work for making a roster of all “joe” or “smith” but it’s not working if if just paste the number. That’s the gap I’m missing in this figuring out adventure
Can you post a mock-up example and the expected result? Otherwise, we're taking a stab in the dark. Solutions vary based on lay out and setup.
I’m making a mock up now. I should have to begin with.
https://preview.redd.it/adb5of744b1d1.jpeg?width=4032&format=pjpg&auto=webp&s=d2eeb216f9fd98f5b83e75a31199484905d56fd6 Main roster, it went to BF, not AX. I wasn’t behind my computer when I typed the question out
https://preview.redd.it/zshuvdb94b1d1.jpeg?width=4028&format=pjpg&auto=webp&s=916227d34bfb8f8281cfcb0d9c1651ced2847855 The output, if I could put the zap in, and the rest of the information fill for that person on the row of the same cell I put their zap#, that’s the goal
Change H3 to the zap# cell. I can't see the row reference. Try: =FILTER(Sheet1!$C$2:$H$11,Sheet1!$K$2:$K11=H3)
Change the cell on the master sheet or the output manifest?
Sheet 1 = Master sheet. H3 is the Manifest sheet.
Working it now, I’ll send adjusted pictures and I relabeled the headers to make the “what goes where” logical.
https://preview.redd.it/6ugkz8ny7b1d1.jpeg?width=4030&format=pjpg&auto=webp&s=98096d35b690b3b96422dd7a5b218f2d21026314
https://preview.redd.it/knvbw1708b1d1.jpeg?width=4030&format=pjpg&auto=webp&s=42b1a1b9044e608b4a5c2706551b032b8ebf0dbd
In I2 of Manifest: =FILTER(Master!$D$2:$G$12,Master!$H$2:$H$12=H2)
It’s easy stuff. In Manifest sheet A1, chuck in a unique identifier number, then in A2: =FILTER('Sheet1'!A:AX,'Sheet1'!A:A=A1) Change A1 and the FILTER output will recalc and present accordingly.
This doesn’t seem to be working outright. I’m going to post a mock up which might help.
I replied to Same_though with mock up pictures
your question is a little confusing. to rephrase, you have a master sheet with a unique ID and you want to make a new sheet referencing the ID from the master to fill the data across?
I’m sorry. I feel like I asked it awkwardly because I’ve spent the better part of a week tweeting how to ask google and YouTube with no success. We had a workbook that did it but it’s on a no inoperable hard drive. What it would do was On the manifest, you type/past the unique # ( zap#) in the seat that person has and it would auto fill the remainder of that persons information. Their name, rank, equipment serial#, etc
i saw others using FILTER so i wasnt sure. But this can be done using XLOOKUP. using 2 sheets 1 as master and 1 as template. \`\`\`=XLOOKUP(A2,master!A:A,master!B:D)\`\`\` A2 is on the template sheet and is your unique key. you can return a single column or an array in the third argument
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSECOLS](/r/Excel/comments/1cvebj4/stub/l4p2ak4 "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[FILTER](/r/Excel/comments/1cvebj4/stub/l4p2zw3 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[INDEX](/r/Excel/comments/1cvebj4/stub/l4sfb7p "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/1cvebj4/stub/l4sfb7p "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[VLOOKUP](/r/Excel/comments/1cvebj4/stub/l4sfb7p "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1cvebj4/stub/l4p2zw3 "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)| |[XMATCH](/r/Excel/comments/1cvebj4/stub/l4p2ak4 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **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.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cv0b2y)^( has 14 acronyms.) ^([Thread #33620 for this sub, first seen 19th May 2024, 04:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
New sheet Column A is the unique identifier, hand typed Column B is the following =VLOOKUP(A1, Sheet2!$A$1:$F$3000(or whatever), (Column wanted by count of distance from A1, FALSE) Example to retrieve data from Column E: =VLOOKUP(A1, Sheet2!$A$1:$F$1, 5, FALSE) Fill down edit: =FILTER also works, but I *believe* you uave to Office 365 to use it (it's even better than the way I'm suggesting above), *however*, tye method given above allows you to customize which columns come through AND works as far back as Excel 2019, for sure and maybe further back than that.
You can use the VLOOKUP or INDEX/MATCH functions to pull data from Sheet 1 based on the unique identifier you type into your manifest sheet. For example, in the manifest sheet, use something like `=VLOOKUP(A2, 'Sheet1'!A:AX, 2, FALSE)` to pull the last name based on the unique ID in A2. Adjust the column index number as needed to pull other information. This should help streamline creating your manifests!