Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel match

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel 2010 , MATCH / transfer of data from master to smaller fil

Excel 2010 , MATCH / transfer of data from master to smaller fil

ResolvedVersion 2010

Stephen has attended:
Excel Intermediate course
Excel Advanced course

Excel 2010 , MATCH / transfer of data from master to smaller fil

Hi guys ,

Next Q for you ..... Scenario is to transpose certain columns of a master file data (tab 2) into a smaller consolidated table (tab 1)

A subset of data below , but this is 1500+ rows of data

the ? denotes the match up I need to do (Positive or negative to <tab 2>)

So TAB 1 'Merged Sheet'

Column A H I J K

Code Branch 4 Branch 4 Branch 6 Branch 6
Previous Rate Previous Rate
DR ? ? ? ?
CE ? ? ? ?
31 ? ? ? ?
32 ? ? ? ?
J6 ? ? ? ?

Tab 2 'Master Branch'

The is the master data that needs to be lifted and matched where applicable to the above from columns G & L (1578 rows)

Column G L
Branch Code
4 DR
6 DR
6 CE
4 J6

So in the example above Code DR should populate across all instances of branch 4 & 6 (Columns H-K) , whereas code CE should populate just against Branch 6 (Columns J-K) .

As I say , this is just a subset , the branches go right out to branch 31 , and the 'codes' have 1578 permutations . But I think by doing the small subset above , I can then build out the rest of the formula across the remainder of the large table in TAB 1 .

tks in advance as always .

Stephen


RE: Excel 2010 , MATCH / transfer of data from master to smaller

Hi Stephen,

Thank you for your question. Would it be at all possible to email us a sample spreadsheet containing an extract of the data? That would simply give us a better understanding of the layout and we can also then make sure the formulas work properly.

Please email a sample to forum@stl-training.co.uk

Kind regards
Marius Barnard

Excel tip:

Stop Formula Returning A "#DIV/0" Error

If a formula returns a #DIV/0 error message there is a way to avoid such results.

For example the formula =A1/B1 will return a #DIV/0 if B1 is empty or a zero.

If you protect your formulas with the ISERROR function, the formula will then look like this:

=IF(ISERROR(A1/B1),0,A1/B1)

In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of A1/B1.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.