Help with Recognizing new text

[spalmer]

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.

[#2]

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.

[#3]

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 

[#4 · Accepted 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.

[#5]

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

[#6]

You're welcome@spalmer!

[#7]

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

[#8]

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

[#9]

that did the trick thank you @Subodh_Tiwari_sktneer 

[#10]

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