Copy data from 2 sheets to others based on criteria

[caleb911]

Hi Team,

I have an inquiry similar to the following

https://techcommunity.microsoft.com/t5/Excel/Copy-Data-to-Other-Sheets-Columns-Based-on-Criteria/td-...

I have a workbook with 5 worksheets. 1 for each of 2 salespersons, and 1 for each of 3 locations.

I'd like each of the salespeople to fill in their sheet - with a field for the location, and then have the data automatically populate in to the location sheets.

Using the Array formula solution I have it going from one salesperson to the three locations no worries, but can't get it to go from the 2 sales people to three.

The Array formula is my preferred solution as it updates automatically without a button click for the user.

It seems like the loop count for the second IF is starting from where the first left off maybe?

[#2]

@caleb911 

I changed George St. cell D7 from a CSE formula to a regular formula that handles both Jack and Liam. The AGGREGATE function requires Excel 2010 or later, and has similar functionality to SMALL but doesn't need the CSE.

Note that the formula returns 0 when the target cell is blank. You may suppress those zeros in columns N:Q by using a Custom number format #;-#;;@. I don't have a good tweak for your conditional formatting in column  R to get the pink color, however.

=IFERROR(
INDEX(Jack!D$7:D$29,
AGGREGATE(15,6,
(ROW(Jack!$C$7:$C$29) - ROW(Jack!$C$7)+1)/(Jack!$C$7:$C$29=$T$4),
ROWS(D$7:D7)
)
),
IFERROR(
INDEX(Liam!D$7:D$29,
AGGREGATE(15,6,
(ROW(Liam!$C$7:$C$29) - ROW(Liam!$C$7)+1)/(Liam!$C$7:$C$29=$T$4),
ROWS(D$7:D7) - COUNTIF(Jack!$C$7:$C$29,$T$4)
)
),""
)
)

[#3]

@Brad Yundt Oh man that is so much tidier than what I've just finished in the last hour.

I used the one to one for 2 tables in each of the location sheets, then stacked each column in the location sheet's table proper. 

Thanks very much Brad!

[#4]

@caleb911 

I honestly cannot decide whether to be deeply impressed by your formulae and their layout or horrified at the choice of tools selected for the job!

In PowerQuery all that is required is to append the input tables and filter to give the output tables.  I appreciate that you want a more dynamic behaviour and so are attracted to tables.  If so, I suspect that this one workbook by itself would justify a copy of Office 365 to gain access to the new dynamic array functionality (at the moment only 'insider' versions but expected for wider release any time now).

The formula I used to combine your salesperson data was 

= CHOOSE( s#,

INDEX( Jack, recordNum#, columnNum# ),

INDEX( Liam, recordNum#, columnNum# ) )

This table has named sub-ranges 'combinedBranch' and 'combinedData' giving the final formula

= FILTER( combinedData, combinedBranch="George St" )

on your George St sheet.

Each formula occupied a single cell but they spill to fill their respective table.