SOLVED
Home

Duplicating Information Formula Help Please on Large Data Spreadsheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-566309%22%20slang%3D%22en-US%22%3EDuplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-566309%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20spreadsheet%20attached%20has%20different%20Districts%20(column%20A)%20-%20Column%20D-K%20will%20always%20be%20the%20same%20values%20and%20comments%20for%20the%20same%20person%20named%20in%20columns%20B%20and%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20without%20filtering%20and%20dragging%20the%20data%20under%20the%20candidate%20name%20%22which%20is%20time%20consuming%22%20(and%20as%20the%20original%20spreadsheet%20has%20over%203000%20names%20on%20it)%20-%20Is%20there%20a%20formula%20I%20could%20enter%20so%20the%20spreadsheet%20recognises%20the%20name%20of%20the%20person%20and%20once%20one%20line%20is%20complete%20it%20%22finds%22%20and%20%22populates%22%20the%20same%20name%20%22with%20the%20same%20information%22%20in%20other%20districts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-566309%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-567927%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-567927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284371%22%20target%3D%22_blank%22%3E%40Gwads1968%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20D12%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(%24D%245%3A%24K11%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(%24B12%26amp%3B%22%7C%22%26amp%3B%24C12%2CINDEX(%24B%245%3A%24B11%26amp%3B%22%7C%22%26amp%3B%24C%245%3A%24C11%2C0)%2C0)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECOLUMN()-3)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-568169%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%2C%20Sir%2C%20are%20a%20Genius%20-%20Many%20Thanks%20for%20your%20Guidance%20and%20Formula%20-%20It%20works%20a%20treat.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-568204%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568204%22%20slang%3D%22en-US%22%3EIt's%20my%20pleasure%20to%20help%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570342%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20-%20I%20have%20one%20small%20change%20and%20need%20an%20update%20on%20the%20lookup%20formula%20if%20possible%20please%20-%20previously%20I%20had%20the%20individual%20name%20separate%20(first%20name-surname)%20-%20I%20now%20will%20be%20having%20the%20Full%20name%20in%20one%20cell%20which%20has%20impacted%20your%20formula%20-%20if%20you%20get%20chance%20can%20you%20just%20make%20the%20necessary%20adjustment%20-%20I%20have%20tried%20but%20can't%20figure%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanking%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581551%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EHi%20-%20Many%20thanks%20for%20your%20previous%20help%20-%20I%20have%20one%20small%20change%20and%20need%20an%20update%20on%20the%20lookup%20formula%20if%20possible%20please%20-%20previously%20I%20had%20the%20individual%20name%20separate%20(first%20name-surname)%20-%20I%20now%20will%20be%20having%20the%20Full%20name%20in%20one%20cell%20which%20has%20impacted%20your%20formula%20-%20if%20you%20get%20chance%20can%20you%20just%20make%20the%20necessary%20adjustment%20-%20I%20have%20tried%20but%20can't%20figure%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help%20-%20Thanking%20you%20in%20advance.%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22AddMessageTags%20lia-message-tags%20lia-component-message-view-widget-tags%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-584335%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-584335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284371%22%20target%3D%22_blank%22%3E%40Gwads1968%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20lookup%20value%20is%20now%20the%20full%20name%20instead%20of%20the%20first%20and%20last%20names%2C%20a%20simple%20VLOOKUP%20would%20do%20the%20job.%20The%20formula%20in%20C12%2C%20copied%20down%20rows%20and%20across%20columns%2C%20would%20be%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24B12%2C%24B%245%3A%24J11%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECOLUMN()-1%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20it%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612636%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EThank%20You%20-%20This%20is%20the%20actual%20spreadsheet%20(sample)%20The%20cells%20of%20interest%20are%20the%20%22Name%22%20and%20then%20cells%20%22I-P%22%20-%20can%20you%20possibly%20change%20the%20look%20up%20formula%20to%20match%20this%20exact%20spreadsheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards.%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22LabelsForArticle%20lia-component-labels%20lia-component-message-view-widget-labels-with-event%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-614954%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicating%20Information%20Formula%20Help%20Please%20on%20Large%20Data%20Spreadsheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-614954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F284371%22%20target%3D%22_blank%22%3E%40Gwads1968%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20version%20of%20your%20file%2C%20the%20formula%20in%20I8%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24C8%2C%3CBR%20%2F%3E%24C%244%3A%24P%3CSTRONG%3E7%3C%2FSTRONG%3E%2C%3CBR%20%2F%3ECOLUMN()-%3CSTRONG%3E2%3C%2FSTRONG%3E%2C0)%3C%2FP%3E%3CP%3ENote%20that%20Row%20%3CSTRONG%3E7%3C%2FSTRONG%3E%20in%20the%20table_array%20argument%20is%20relative%20so%20that%20it%20will%20adjust%20to%20the%20row%20above%20the%20formula%20cell%20whenever%20the%20formula%20is%20copied%20down%20rows.%20Conversely%2C%20the%20%3CSTRONG%3E2%3C%2FSTRONG%3E%20column%20reduction%20of%20the%20col_index_num%20argument%20represents%20the%20%3CSTRONG%3E2%3C%2FSTRONG%3E%20Columns%20preceding%20the%20table%20array%2C%20which%20are%20%3CSTRONG%3EColumns%20A%20and%20B%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gwads1968
Occasional Contributor

The spreadsheet attached has different Districts (column A) - Column D-K will always be the same values and comments for the same person named in columns B and C.

 

Basically without filtering and dragging the data under the candidate name "which is time consuming" (and as the original spreadsheet has over 3000 names on it) - Is there a formula I could enter so the spreadsheet recognises the name of the person and once one line is complete it "finds" and "populates" the same name "with the same information" in other districts.

 

I hope that makes sense.

8 Replies
Solution

@Gwads1968 

In the attached file, the formula in D12, copied down rows and across columns, is: 

=INDEX($D$5:$K11,
MATCH($B12&"|"&$C12,INDEX($B$5:$B11&"|"&$C$5:$C11,0),0),
COLUMN()-3)

@Twifoo 

 

You, Sir, are a Genius - Many Thanks for your Guidance and Formula - It works a treat.

It's my pleasure to help you.

@Twifoo 

 

Hi - I have one small change and need an update on the lookup formula if possible please - previously I had the individual name separate (first name-surname) - I now will be having the Full name in one cell which has impacted your formula - if you get chance can you just make the necessary adjustment - I have tried but can't figure it out.

 

Thanking you in advance.

@Twifoo 

 

Hi - Many thanks for your previous help - I have one small change and need an update on the lookup formula if possible please - previously I had the individual name separate (first name-surname) - I now will be having the Full name in one cell which has impacted your formula - if you get chance can you just make the necessary adjustment - I have tried but can't figure it out.

 

I hope you can help - Thanking you in advance.

 
 

@Gwads1968 

If the lookup value is now the full name instead of the first and last names, a simple VLOOKUP would do the job. The formula in C12, copied down rows and across columns, would be: 

=VLOOKUP($B12,$B$5:$J11,
COLUMN()-1,0)

See it in the attached file.

@Twifoo 

 

Thank You - This is the actual spreadsheet (sample) The cells of interest are the "Name" and then cells "I-P" - can you possibly change the look up formula to match this exact spreadsheet?

 

 

 

Kind Regards.

 
 

@Gwads1968 

In the attached version of your file, the formula in I8, copied down rows and across columns, is: 

=VLOOKUP($C8,
$C$4:$P7,
COLUMN()-2,0)

Note that Row 7 in the table_array argument is relative so that it will adjust to the row above the formula cell whenever the formula is copied down rows. Conversely, the 2 column reduction of the col_index_num argument represents the 2 Columns preceding the table array, which are Columns A and B

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies