• 512K Members
• 6,751 Online
• 608K Conversations

New Contributor

# Copy data from 2 sheets to others based on criteria

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?

3 Replies
Highlighted

# Re: Copy data from 2 sheets to others based on criteria

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)
)
),""
)
)

# Re: Copy data from 2 sheets to others based on criteria

@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.

# Re: Copy data from 2 sheets to others based on criteria

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" )

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

Related Conversations
How to copy or duplicate an existing forms quiz
Olaf Nennker in Microsoft Forms on
2 Replies
Pulling data from one tab to another
krysphares in Excel on
6 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Separate lines on Sheets depending on condition
Mohammed Almaghi in Excel on
1 Replies