SOLVED
Home

Possibly simple solution

%3CLINGO-SUB%20id%3D%22lingo-sub-1007143%22%20slang%3D%22en-US%22%3EPossibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1007143%22%20slang%3D%22en-US%22%3EHi%20All%2C%20this%20is%20probably%20a%20simple%20request%20but%20I%20can't%20get%20my%20head%20around%20this%20tonight!%3CBR%20%2F%3EI%20need%20to%20get%202%20columns%20of%20data%20to%20duplicate%20in%20certain%20areas%20when%20assigned%20by%20a%20number.%20I've%20added%20a%20picture%20because%20I'm%20not%20sure%20if%20I'm%20explaining%20it%20right.%3CBR%20%2F%3ESo%20in%20short%20I%20want%20the%20first%20name%20and%20last%20name%20to%20appear%20under%20the%20green%20'1'%20when%20I%20type%20in%201%20in%20the%20table%20number%20column.%20Is%20this%20possible%3F%20There%20are%20multiple%20numbers%20to%20assign%20but%20if%20I%20can%20get%20the%20first%20one%20sorted%20i%20can%20go%20from%20there!%20Please%20help!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1007143%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-1007167%22%20slang%3D%22en-US%22%3ERe%3A%20Possibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1007167%22%20slang%3D%22en-US%22%3EIn%20F2%20type%3CBR%20%2F%3E%3D%20IF(%20D2%3D%24F%241%2C%20B2%2C%20%22%22)%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20G2%20type%3CBR%20%2F%3E%3D%20IF(%20D2%3D%24F%241%2C%20C2%2C%20%22%22)%3CBR%20%2F%3E%3CBR%20%2F%3EThen%2C%20drag%20the%20formulas%20down%20to%20the%20other%20rows.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20want%20to%20have%20First%20and%20Last%20name%20in%20just%20one%20cell%2C%20type%20in%20F2%3A%3CBR%20%2F%3E%3D%20IF(%20D2%3D%24F%241%2C%20B2%20%26amp%3B%20%22%20%22%20%26amp%3B%20C2%2C%20%22%22)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20I%20understood%20your%20requirement%20correctly.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1049203%22%20slang%3D%22en-US%22%3ERe%3A%20Possibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3EThankyou%20for%20this%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20my%20only%20problem%20is%20now%20that%20it%20will%20only%20put%20in%20the%20first%2010%20names%20in%20the%201%20section%2C%20how%20do%20I%20adjust%20the%20formula%20to%20make%20the%20names%20appear%20under%20other%20number%20sections%20when%20entering%206%20next%20to%20the%20names%20or%20any%20other%20numbers%3F%20You%20can%20see%20the%206%20section%20in%20the%20picture%20from%20before.%20I've%20tried%20to%20adjust%20the%20formulas%20but%20its%20not%20working.%20Does%20this%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1049235%22%20slang%3D%22en-US%22%3ERe%3A%20Possibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F457215%22%20target%3D%22_blank%22%3E%40Loobydoodles%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20unmerge%20the%20columns%20F%20and%20G%20if%20they%20are%20merged%20and%20then%20try%20the%20following%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3E%20(which%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20instead%20of%20Enter%20alone)%20in%20%3CSTRONG%3EF2%3C%2FSTRONG%3E%20and%20copy%20it%20down%20as%20per%20your%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20F2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24B%242%3A%24B%2422%26amp%3B%22%20%22%26amp%3B%24C%242%3A%24C%2422%2CSMALL(IF(%24D%242%3A%24D%2422%3D%24F%241%2CROW(%24B%242%3A%24B%2422)-ROW(%24B%242)%2B1)%2CROWS(F%242%3AF2)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EConfirm%20the%20above%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E.%20When%20you%20confirm%20a%20formula%20as%20an%20Array%20Formula%2C%20you%20will%20notice%20in%20the%20formula%20bar%20that%20your%20formula%20gets%20surrounded%20by%20curly%20braces.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20per%20the%20formula%20if%20you%20input%201%20in%20F1%2C%20you%20will%20get%20all%20the%20names%20where%20column%20D%20has%201%20and%20if%20you%20input%202%2C%20you%20will%20get%20all%20the%20names%20where%20column%20D%20has%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdjust%20the%20range%20reference%20in%20the%20formula%20as%20per%20your%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20Please%20consider%20uploading%20an%20Excel%20file%20instead%20of%20an%20image%20as%20images%20are%20difficult%20to%20work%20with.%20The%20advantage%20of%20an%20Excel%20file%20is%2C%20we%20can%20implement%20the%20working%20solution%20in%20your%20file%20and%20send%20back%20to%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1049370%22%20slang%3D%22en-US%22%3ERe%3A%20Possibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3EHiya%2C%20thankyou%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20still%20struggling%20with%20it%2C%20I%20really%20need%20to%20brush%20up!%20I%20have%20attached%20the%20document%20if%20you%20are%20able%20to%20implement%20the%20working%20solution%20in%20the%20file%20please%20that%20would%20be%20fantastic%3F!%20I%20literally%20just%20need%20the%20first%20and%20last%20name%20to%20appear%20under%20the%20table%20number%20headings%20when%20a%20corresponding%20number%20is%20typed%20in%20D2.%3C%2FP%3E%3CP%3EThere%20are%20still%20names%20to%20go%20on%20the%20sheet%20so%20I%20have%20extended%20the%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1049417%22%20slang%3D%22en-US%22%3ERe%3A%20Possibly%20simple%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F457215%22%20target%3D%22_blank%22%3E%40Loobydoodles%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20with%20the%20formulas%20in%20place.%20You%20only%20need%20to%20enter%20the%20table%20number%20in%20column%20D%20and%20the%20names%20will%20be%20populated%20in%20the%20relevant%20table%20automatically%20as%20per%20the%20entered%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20note%20that%20I%20have%20added%20a%20custom%20number%20formatting%20for%20all%20the%20table%20headers%20(green%20cells)%20so%20that%20they%20contain%20only%20a%20number%20but%20a%20prefix%20of%20Table%20is%20added%20to%20all%20of%20them.%20e.g.%20if%20you%20look%20at%20the%20cell%20F1%2C%20it%20contains%20a%20number%201%20but%20the%20cell%20shows%20you%20the%20text%20%22Table%201%22%20where%20the%20%22Table%22%20prefix%20is%20added%20through%20the%20custom%20number%20formatting%20whereas%20the%20actual%20cell%20content%20is%20only%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20original%20question%2C%20please%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%2FAnswer%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Loobydoodles
New Contributor
Hi All, this is probably a simple request but I can't get my head around this tonight!
I need to get 2 columns of data to duplicate in certain areas when assigned by a number. I've added a picture because I'm not sure if I'm explaining it right.
So in short I want the first name and last name to appear under the green '1' when I type in 1 in the table number column. Is this possible? There are multiple numbers to assign but if I can get the first one sorted i can go from there! Please help!
5 Replies
In F2 type
= IF( D2=$F$1, B2, "")

In G2 type
= IF( D2=$F$1, C2, "")

Then, drag the formulas down to the other rows.

If you want to have First and Last name in just one cell, type in F2:
= IF( D2=$F$1, B2 & " " & C2, "")

I hope I understood your requirement correctly.

@Celia_AlvesThankyou for this my only problem is now that it will only put in the first 10 names in the 1 section, how do I adjust the formula to make the names appear under other number sections when entering 6 next to the names or any other numbers? You can see the 6 section in the picture from before. I've tried to adjust the formulas but its not working. Does this make sense?

@Loobydoodles 

First of all, unmerge the columns F and G if they are merged and then try the following Array Formula (which requires confirmation with Ctrl+Shift+Enter instead of Enter alone) in F2 and copy it down as per your requirement.

 

In F2

=IFERROR(INDEX($B$2:$B$22&" "&$C$2:$C$22,SMALL(IF($D$2:$D$22=$F$1,ROW($B$2:$B$22)-ROW($B$2)+1),ROWS(F$2:F2))),"")

Confirm the above formula with Ctrl+Shift+Enter. When you confirm a formula as an Array Formula, you will notice in the formula bar that your formula gets surrounded by curly braces.

 

So as per the formula if you input 1 in F1, you will get all the names where column D has 1 and if you input 2, you will get all the names where column D has 2.

 

Adjust the range reference in the formula as per your requirement.

 

Note: Please consider uploading an Excel file instead of an image as images are difficult to work with. The advantage of an Excel file is, we can implement the working solution in your file and send back to you.

@Subodh_Tiwari_sktneerHiya, thankyou for this.

 

I'm still struggling with it, I really need to brush up! I have attached the document if you are able to implement the working solution in the file please that would be fantastic?! I literally just need the first and last name to appear under the table number headings when a corresponding number is typed in D2.

There are still names to go on the sheet so I have extended the range.

 

Many Thanks in advance

 

Solution

@Loobydoodles 

Please find the attached with the formulas in place. You only need to enter the table number in column D and the names will be populated in the relevant table automatically as per the entered number.

 

Also, note that I have added a custom number formatting for all the table headers (green cells) so that they contain only a number but a prefix of Table is added to all of them. e.g. if you look at the cell F1, it contains a number 1 but the cell shows you the text "Table 1" where the "Table" prefix is added through the custom number formatting whereas the actual cell content is only 1.

 

If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies