SOLVED
Home

Help with Recognizing new text

%3CLINGO-SUB%20id%3D%22lingo-sub-873426%22%20slang%3D%22en-US%22%3EHelp%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873426%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20figure%20out%20if%20excel%20can%20recognize%20new%20text%20in%20a%20column%20of%20cells%20as%20I%20type%20it%20in.%26nbsp%3B%20I%20attached%20a%20document%20below%26nbsp%3Bto%20help.%26nbsp%3B%20As%20you%20can%20see%20I%20have%20two%20columns%3A%20one%20is%20a%20%22JOB%20CODE%22%20the%20other%20%22TOTAL%22.%26nbsp%3B%20As%20I%20type%20a%20new%20JOB%20CODE%20in%20column%20B%26nbsp%3B(%3CEM%3Ethat%20hasn't%20been%20inputted%20in%20E4%20and%20E5%20already%3C%2FEM%3E)%2C%20I%20want%20excel%20to%20recognize%20that%20there%20is%20a%20new%20JOB%20CODE%20and%20automatically%20insert%20that%20in%20E6%20without%20me%20having%20to%20input%20that%20manually.%26nbsp%3B%20any%20help%20would%20be%20greatly%20appreciated.%26nbsp%3B%20thank%20you%20everyone.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-873426%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-873613%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873613%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhat%20you%20describe%20can%20be%20achieved%20with%20a%20pivot%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20turn%20your%20data%20entry%20table%20into%20an%20Excel%20Table%20object%20by%20clicking%20a%20cell%20in%20the%20table%20and%20then%26nbsp%3B%3CSTRONG%3E%20Insert%20%26gt%3B%20Table%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20select%20any%20cell%20in%20the%20table%20and%20click%26nbsp%3B%3CSTRONG%3EInsert%20%26gt%3B%20Pivot%20Table.%26nbsp%3B%3C%2FSTRONG%3E%20Use%20the%20dialog%20to%20insert%20the%20pivot%20table%20on%20the%20existing%20worksheet.%20In%20the%20PivotTable%20Fields%20pane%20drag%20the%20Job%20Code%20into%20the%20Rows%20area%20and%20the%20Total%20into%20the%20Values%20area.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20enter%20new%20data%20into%20the%20data%20entry%20table%20and%20refresh%20the%20pivot%20table%20(right-click%20any%20field%20in%20the%20pivot%20table%20and%20select%20Refresh).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20already%20have%20the%20new%20Dynamic%20Array%20formulas%2C%20you%20can%20also%20use%20formulas.%20In%20the%20screenshot%20the%20formulas%20are%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ein%20cell%20H5%20is%26nbsp%3B%20%3DUNIQUE(Table1%5BJOB%20CODE%3A%5D)%3C%2FP%3E%0A%3CP%3Ein%20cell%20I5%26nbsp%3B%3DSUMIFS(C%3AC%2CB%3AB%2CH5%23)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20formulas%20have%20not%20been%20copied%20down%20and%20will%20extend%20automatically%20when%20new%20data%20is%20entered.%20No%20refresh%20required.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20772px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133398i4ADB2BE679239DAC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-25_14-04-56.png%22%20title%3D%222019-09-25_14-04-56.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873636%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873636%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20help%20Ingeborg.%26nbsp%3B%20I%20don't%20have%20access%20to%20the%20new%20dynamic%20array%20formulas%20yet%2C%20but%20that%20UNIQUE%20formula%20will%20do%20the%20trick.%26nbsp%3B%20ill%20just%20have%20to%20be%20patient%20haha.%26nbsp%3B%20Again%20thank%20you%20so%20much%20for%20the%20help.%26nbsp%3B%20much%20appreciated.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873671%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20don't%20have%20access%20to%20the%20dynamic%20formulas%2C%20you%20may%20try%20one%20of%20the%20methods%20shown%20in%20three%20different%20tabs%20in%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873675%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873675%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20You%20Subodh!%26nbsp%3B%20those%20should%20work%20as%20well.%26nbsp%3B%20thank%20you%20for%20your%20time%20and%20help%20with%20this.%26nbsp%3B%20I%20really%20appreciate%20it!%20%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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873723%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873723%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875126%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875126%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20tried%20your%20formulas%20and%20they%20aren't%20working%20on%20my%20end.%26nbsp%3B%20I%20attached%20the%20document%20and%20on%20sheet%202%20maybe%20you%20can%20help%20me%20out%20with%20what%20im%20doing%20wrong.%26nbsp%3B%20the%20way%20I%20have%20everything%20placed%20on%20sheet%202%20is%20exactly%20like%20my%20official%20document%20I%20have.%26nbsp%3B%20thank%20you%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875150%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875150%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20in%20K6%20to%20get%20the%20unique%20job%20code%20is%20an%20array%20formula%20which%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20instead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20resolves%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875236%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875236%22%20slang%3D%22en-US%22%3E%3CP%3Ethat%26nbsp%3Bdid%26nbsp%3Bthe%26nbsp%3Btrick%26nbsp%3Bthank%26nbsp%3Byou%26nbsp%3B%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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875243%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Recognizing%20new%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875243%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
spalmer
Contributor

I'm trying to figure out if excel can recognize new text in a column of cells as I type it in.  I attached a document below to help.  As you can see I have two columns: one is a "JOB CODE" the other "TOTAL".  As I type a new JOB CODE in column B (that hasn't been inputted in E4 and E5 already), I want excel to recognize that there is a new JOB CODE and automatically insert that in E6 without me having to input that manually.  any help would be greatly appreciated.  thank you everyone.

9 Replies

Hello @spalmer ,

 

what you describe can be achieved with a pivot table.

 

First, turn your data entry table into an Excel Table object by clicking a cell in the table and then  Insert > Table

Next, select any cell in the table and click Insert > Pivot Table.  Use the dialog to insert the pivot table on the existing worksheet. In the PivotTable Fields pane drag the Job Code into the Rows area and the Total into the Values area. 

 

Now enter new data into the data entry table and refresh the pivot table (right-click any field in the pivot table and select Refresh).

 

If you already have the new Dynamic Array formulas, you can also use formulas. In the screenshot the formulas are

 

in cell H5 is  =UNIQUE(Table1[JOB CODE:])

in cell I5 =SUMIFS(C:C,B:B,H5#)

 

These formulas have not been copied down and will extend automatically when new data is entered. No refresh required.

 

2019-09-25_14-04-56.png

 

 

 

 

Thank you for the help Ingeborg.  I don't have access to the new dynamic array formulas yet, but that UNIQUE formula will do the trick.  ill just have to be patient haha.  Again thank you so much for the help.  much appreciated.

@Ingeborg Hawighorst 

Solution

@spalmer 

Since you don't have access to the dynamic formulas, you may try one of the methods shown in three different tabs in the attached.

 

Thank You Subodh!  those should work as well.  thank you for your time and help with this.  I really appreciate it! @Subodh_Tiwari_sktneer 

You're welcome@spalmer!

 

 

Hi @Subodh_Tiwari_sktneer 

So I tried your formulas and they aren't working on my end.  I attached the document and on sheet 2 maybe you can help me out with what im doing wrong.  the way I have everything placed on sheet 2 is exactly like my official document I have.  thank you for your time

@spalmer 

Formula in K6 to get the unique job code is an array formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

 

If that resolves 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.

 

 

that did the trick thank you @Subodh_Tiwari_sktneer 

You're welcome @spalmer! Glad it worked as desired.

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
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies