Forum Discussion
Excel Formula Help
Hi everyone, i am needing help pulling data from one sheet to another. The "DATA" tab is a bunch of data that is pulled from a Microsoft forms survey and i copy and paste it in the sheet. Once i copy and past the data i want all the other sheets to pull this data into its perspective tab.
On the "DATA" tab in coloumn G is the district and each district has a different column for the module. So MRD districts module is column I, SLRD districts module is Column J, SBRD districts module is column K and so on. What i would is when data is pasted i want MRD ABC123 to pull the data from the "DATA" sheet onto this tab but it has to meet the 2 criterias. 1st criteria for the "MRD ABC123" tab is the Job Code in Column F on the "DATA" tab has to match ABC123 and the District column G in the "DATA" tab has to match MRD and then pull that data from the row on the "DATA" tab into the perspective columns on the "MRD ABC123" tab. Im just looking for a formula that will work for each column on the "MRD ABC123" tab to pull the data from the "DATA" tab. i have tried Index Match, Xlookup, and Filter functions but i know im doing something wrong.
Any help would be amazing. thank you.
14 Replies
One to many report:
create temp table aa as
select udf_concat(f03,f04,f05,f06,f07,f08) module,* from Data limit 20;
select * from aa;
select f01,f02,group_concat(module||f09||f10||f11||f12||f13) 子表 from aa group by f01,f02;
Thank you for your Help!
Try the following formula-
=LET(x,FILTER(OfficeForms.Table[[Module]:[Comments]],(OfficeForms.Table[District]=A1)*(OfficeForms.Table[Job Code]=C1)), HSTACK(TOROW(TAKE(x,,6),3),TAKE(x,,-5)))Hi Harun24HR, after inputting this into other cells on my actual spreadsheet, i was getting an error.
Thank you! That formula worked!
Example: Pulling the Module column (Column I in DATA)
In the Module column of MRD ABC123 (let’s say column D):
=FILTER(DATA!I:I, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "No matching data")
Explanation:
- DATA!I:I → MRD Module column
- (DATA!G:G="MRD") → District matches MRD
- (DATA!F:F="ABC123") → Job Code matches ABC123
- "No matching data" → optional message if no match
Example: Pulling another column (e.g., Employee Name in Column B)
=FILTER(DATA!B:B, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "")
Step-by-step for all columns on MRD ABC123 tab
- Decide which columns on your tab correspond to which columns on DATA.
- In each column, use:
=FILTER(DATA!<COLUMN>, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "")
- Replace <COLUMN> with the column from DATA you want to pull.
For other districts/tabs (SLRD, SBRD, etc.)
- Change the District and Module column:
SLRD SLR123 Module (Column J in DATA):
=FILTER(DATA!J:J, (DATA!G:G="SLRD")*(DATA!F:F="SLR123"), "")
SBRD SBR123 Module (Column K in DATA):
=FILTER(DATA!K:K, (DATA!G:G="SBRD")*(DATA!F:F="SBR123"), "")
Notes
- Each column uses its own FILTER formula.
- The rows will automatically spill to match all rows from DATA that meet both criteria.
- This works in Excel 365 / Excel 2021+.
My answers are voluntary and without guarantee!
Hope this will help you.
Hi NikolinoDE, so i thought this was going to work but when i enter it into the other cells its only pulling the first data that matches that criteria. Any other thoughts or ideas?
NikolinoDE *See Sheet Attached* this is what it looks like on my sheet
Thank you for your help! This is another formula that worked!!
This will spill all matching rows into your “MRD ABC123” sheet. If no matches, it shows blank.
Tip: Convert your DATA range into a Table so the formula auto-expands when you paste in new survey results.
