SOLVED
Home

Table Swapping

%3CLINGO-SUB%20id%3D%22lingo-sub-812097%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812097%22%20slang%3D%22en-US%22%3E%3CP%3EWould%20you%20be%20able%20upload%26nbsp%3B%20a%20sample%20worksheet%20with%20some%20example%20data%20points%2C%20problem%20areas%20(cell%20reference)%20and%20expected%20results%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20will%20help%20you%20and%20contributors%20here%20to%20get%20a%20quicker%20and%20better%20solution%20for%20you.%26nbsp%3B%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-813426%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BI%20think%20I%20did%20that%20correctly.%20Hopefully%20someone%20can%20figure%20this%20out.%20Thank%20you%20for%20the%20suggestion!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811208%22%20slang%3D%22en-US%22%3ETable%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811208%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20start%2C%20this%20is%20a%20terrible%20process%20I%20have%20inherited.%20I%20am%20in%20discussions%20with%20the%20team%20I'm%20working%20with%20to%20change%20it%20but%20until%20then%20I%20still%20need%20to%20present%20this%20data%20to%20my%20executive%20team.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20some%20difficulties%20with%20a%20few%20tables.%20I%20use%20them%20to%20compare%20old%20deployment%20data%20with%20new%20data.%20Table%201%20is%20static%20containing%20the%20old%20data.%20Table%202%20gets%20data%20from%20a%20table%20on%20a%20wiki%20copied%20into%20it.%20Table%203%20then%20has%20logic%20that%20checks%20to%20see%20if%20Table%202%20has%20any%20new%20data%20and%20if%20so%20displays%20the%20current%20quarter.%20Once%20this%20comparison%20is%20complete%2C%20I%20copy%20the%20data%20in%20Table%203%20into%20Table%201%20using%20the%20%22Values%20(V)%22%20option.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20where%20the%20problem%20exists%20because%20Table%201%20is%20totaling%20all%20of%20the%20deployments%20but%20when%20I%20paste%20in%20the%20data%20from%20Table%203%20it%20totals%20all%20cells%20in%20the%20column.%20COUNTA()%20counts%20the%20blank%20cells%20as%20well.%20If%20I%20manually%20select%20all%20the%20blank%20cells%20and%20hit%20delete%20the%20subtotal%20only%20counts%20cells%20with%20a%20quarter%20displayed.%20There%20are%20no%20spaces%20in%20the%20cells%20and%20if%20I%20use%20COUNTBLANK()%20the%20number%20remains%20the%20same%20whether%20the%20cell%20has%20%22data%22%20pasted%20into%20it%20or%20I%20have%20deleted%20%22it.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20at%20my%20wits'%20end%20with%20this.%20I'm%20trying%20to%20automate%20a%20terrible%20process%20as%20much%20as%20I%20can%20with%20this%20worksheet%20and%20this%20makes%20me%20want%20to%20tear%20my%20hair%20out.%20I%20have%20attached%20a%20sample%20of%20the%20worksheet.%20Any%20insights%20here%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-811208%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-814411%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394619%22%20target%3D%22_blank%22%3E%40SteeltownRiot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20with%20table%203%20formula%20is%20that%20it%20results%20in%20empty%20text%20(%22%22).%20COUNTA%20counts%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcounta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60217%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eempty%20text.%26nbsp%3B%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20avoid%20this%20issue%2C%20you%20can%20use%20following%20formula%20in%20subtotal%20field%20Solution3.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(--(%5BSolution3%5D%26lt%3B%26gt%3B%22%22))%3C%2FP%3E%3CP%3ESimiliarly%20for%20other%20solution%20columns%20.%20Hope%20this%20helps%20!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eps%3A%20If%20you%20are%20using%20subtotal%26nbsp%3B%20to%20sum%20up%20when%20filters%20are%20applied%20on%20table%201%20or%203.%26nbsp%3B%20If%20yes%2C%20the%20behavious%20of%20SUM%20will%20be%20different%20from%20Subtotal.%20Subtotal%20ignores%20the%20rows%20filtered%20out%20and%20SUM%20does%20not.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815714%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3Bthank%20you!%20I%20tried%20that%2C%20but%20I'm%20getting%20%23VALUE!%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20517px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127879iBCAF45E18606ED2C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-08-21%20131608.png%22%20title%3D%22Annotation%202019-08-21%20131608.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20518px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127878i597C2C97D5EEE1DD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-08-21%20131846.png%22%20title%3D%22Annotation%202019-08-21%20131846.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816524%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394619%22%20target%3D%22_blank%22%3E%40SteeltownRiot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECould%20you%20please%20check%20any%20of%20the%20following%20%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUM(--(TABLE1%5BSolution3%5D%26lt%3B%26gt%3B%22%22))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMPRODUCT(--(LEN(TABLE1%5BSolution3%5D)%20%26gt%3B0))%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20any%20of%20these%20do%20not%20work%20%2C%20could%20you%20please%20upload%20the%20excel%20file%20with%20the%20formula%20%3F%26nbsp%3B%20the%20formula%20seems%20to%20work%20for%20me%20%2C%20just%20wanted%20to%20ensure%20that%20this%20is%20not%20an%20office%20version%20issue.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817259%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BThe%20second%20one%20did%20it.%20Thank%20you%20so%20much!%20I've%20attached%20the%20workbook%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817300%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Swapping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394619%22%20target%3D%22_blank%22%3E%40SteeltownRiot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Egood%20to%20hear%20that%20it%20worked%20and%20glad%20i%20could%20help%20!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
SteeltownRiot
New Contributor

To start, this is a terrible process I have inherited. I am in discussions with the team I'm working with to change it but until then I still need to present this data to my executive team.

 

I am having some difficulties with a few tables. I use them to compare old deployment data with new data. Table 1 is static containing the old data. Table 2 gets data from a table on a wiki copied into it. Table 3 then has logic that checks to see if Table 2 has any new data and if so displays the current quarter. Once this comparison is complete, I copy the data in Table 3 into Table 1 using the "Values (V)" option.

 

This is where the problem exists because Table 1 is totaling all of the deployments but when I paste in the data from Table 3 it totals all cells in the column. COUNTA() counts the blank cells as well. If I manually select all the blank cells and hit delete the subtotal only counts cells with a quarter displayed. There are no spaces in the cells and if I use COUNTBLANK() the number remains the same whether the cell has "data" pasted into it or I have deleted "it."

 

I am at my wits' end with this. I'm trying to automate a terrible process as much as I can with this worksheet and this makes me want to tear my hair out. I have attached a sample of the worksheet. Any insights here would be greatly appreciated.

7 Replies

Would you be able upload  a sample worksheet with some example data points, problem areas (cell reference) and expected results ? 

That will help you and contributors here to get a quicker and better solution for you. 

 

 

@Kodipady I think I did that correctly. Hopefully someone can figure this out. Thank you for the suggestion!

@SteeltownRiot 

The issue with table 3 formula is that it results in empty text (""). COUNTA counts empty text. 

 

To avoid this issue, you can use following formula in subtotal field Solution3. 

=SUM(--([Solution3]<>""))

Similiarly for other solution columns . Hope this helps !!

 

ps: If you are using subtotal  to sum up when filters are applied on table 1 or 3.  If yes, the behavious of SUM will be different from Subtotal. Subtotal ignores the rows filtered out and SUM does not.     

 

@Kodipady thank you! I tried that, but I'm getting #VALUE! now.

 

Annotation 2019-08-21 131608.pngAnnotation 2019-08-21 131846.png

Solution

@SteeltownRiot 

 

Could you please check any of the following ? 

=SUM(--(TABLE1[Solution3]<>""))

=SUMPRODUCT(--(LEN(TABLE1[Solution3]) >0)) 

 

If any of these do not work , could you please upload the excel file with the formula ?  the formula seems to work for me , just wanted to ensure that this is not an office version issue. 

@Kodipady The second one did it. Thank you so much! I've attached the workbook again.

@SteeltownRiot 

good to hear that it worked and glad i could help !! 

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