Excel - Filter/Map information from one sheet to another based on specific text
I have the following two sheets in an Excel file: Sheet 1 and 2 and I would like to use Sheet 2 to organize the information from sheet 1.
Sheet 1 is the following:
| A1 Name | B1 Last name | C1 DOB | D1 Fav Color | F1 Team |
|---|---|---|---|---|
| B1 Sam | Smith | 1/1 | Blue | B |
| C1 John | Dee | 2/1 | Green | A |
| D1 Kelly | Huss | 5/1 | Red | B |
Sheet 2 (What I am trying to achieve) Data should be filter based by Team A or B
B2: Team A
| A3 Name | B3 Last name | C3 Fav Color |
|---|---|---|
| John | Dee | Green |
B31: Team B
| A3 Name | B3 Last name | C3 Fav Color |
|---|---|---|
| Sam | Smith | Blue |
| Kelly | Huss | Red |
How can I Achieve this? (I have look into most suggested answers and tried most of them but not luck) Thank you all
Solution
Simple FILTER by Team column. Wrap it by CHOOSECOLS to extract needed columns:
=CHOOSECOLS(FILTER($A$2:$E$4,$E$2:$E$4=B7),1,2,4)
Adapt to your ranges.
- Excel VBA function to print an array to the workbook
- How do I copy/paste Interior Colors only in Excel?
- Any way to get the real last row not affected by autofilter? Without altering current autofilter status, using UsedRange nor looping cells from bottom
- Using tables and column titles in VLOOKUP
- Auto align data of an Excel table
- Office VSTO Add-ins vs Office Add-ins using Office JS API
- Excel macro to highlight all cells that match value in current cell
- How do I apply conditional formatting in vba?
- Can you display a SQL Server Image datatype column in Excel?
- Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
- Workbook.UpdateFromFile stops runtime
- Merging annotation with differential expression output
- Append to Text File VBA
- Can I check if cells are a Date and change their format?
- Application.OnTime Delays When There Is No User Interaction (Even if Excel Is Active)
- Using Excel VBA (Name statement) to change file extension from .txt to .csv, getting Run-time error '75': Path/File access error
- How to AutoFill using data in every third row?
- Why does Add-Content translates the comma separator from .TotalSeconds into a dot separator?
- How to change the case of a character in a specific expression, using regex?
- Count days within a date range that fall into selected months (via checkboxes)
- Put a user form directly on the excel sheet
- Changing chart types with vba
- How do I find min value of a range
- Return URL From First Search Result
- Change a macro to use late bindings
- How to push data from excel to SQL Server?
- Excel's fullname property with OneDrive
- Using Excel while macros are running
- How to find cells that contain images with openpyxl
- Excel dynamic linest