Jump to content


Photo

Deleting Unwanting Rows & Columns in same Cell Range


  • Please log in to reply
1 reply to this topic

#1 sen

sen

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 03 April 2017 - 02:32 AM

Hi,

Good days.

May I pose a question regarding deleting blank ( unwanted) rows and columns in the same cell range.

Assuming a corporate group has 10 separate and individual companies (Company A – J). And there is 8 different types of Fee (Fees 1 – 8) being charged among the companies. As a group policy, respective company must use the standard template that is designed to covers all the 10 companies and 8 types of Fee.

 

However, not all the companies will charged (or be charged with) all or same type of fee, and therefore many cells are empty as shown in the light green shaded table in the file attached.

 

 I am using in particular, Company A as an illustrative example in the attached file ( in matrix presentation), Company A ( the charging Co) only charge Fee 1, 3, 4,5,8 to Company B,E,G,H & I.

This has caused the necessity to delete the blank rows and columns so as to generate the final presentation as shown in the light orange colour shaded table.

 

It seems this can not be done by using “Find & Select”, “Goto Special”, “Blank”,”Delete  Sheet Rows/Columns” functions.

May you please advise on this.

 

Thanks and cheers.

Attached Files



#2 Mike

Mike

    Advanced Member

  • Administrators
  • 166 posts

Posted 05 April 2017 - 12:12 PM

HI Sen,

 

I've taken a look and I think a pivot table is the solution. Pivot tables enable you to quickly create summaries of large amounts of data.

 

You can see how to create a pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

It won't be possible to create a pivot table based on your existing worksheet because the data is already in a 'pivoted' format similar to a pivot table. The solution is to rearrange the data into a simpler table format and then use that to generate the pivot table.

 

I have attached an example showing how you could apply this approach to your data:

 

Attached File  Delete Unwanted Rows and Columns 2.xlsx   12.82KB   17 downloads

Delete Unwanted Rows and Columns 2.png

 

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.


Mike Smart is the author of ten world best-selling Excel books. The books are available in printed form for for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Mike has also recorded over 850 video lessons (that you can watch online) for Excel 2007, Excel 2010 and Excel 2013.
Mike Smart is also part of the team that answers questions posted on the ExcelCentral.com forums.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users