logo-wlogologo-wlogo-w
  • Home
  • Classes
  • Training
  • Consulting
  • Blog
  • Contact
✕
SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
December 9, 2021

Stacking in EXCEL

December 16, 2021

Stacking in EXCEL

Recently I received data in a pdf file. The snapshot of the data looked like this:

1

Here we have tables containing the same columns i.e. Serial no., Pin code and Type. There were 182 pages of this data. The output should have all data in only 3 columns in an Excel sheet.

Here’s what I did:

1. Copy all the tables at once. Use CTRL+A shortcut.

2. Open an Excel workbook.

3. Paste the tables: CTRL+V.

The pasted values looked like this:

The first row of all the three tables are pasted in a single cell.

01
02

1. Now select the entire column > Go to DATA tab > Text to column > Delimited > Next.

2. Select Space in the Delimiters options.

3. In the data preview we see the data is now separated into different columns.

4. Click Next & Finish.

The table looked like this (Excel screenshot). The last line of the table contains 9356.

5. So we cut and paste the columns from D to I (marked in blue) below it and then again repeat it for the last three columns.

03
04
05

After cut and paste the table was like this:

06

Here row number should have been 28069 (extra one for the Column Headers at the first). On zooming out it was found out that there were some rows that had these headers (S.N., PINCODE, and TYPE). On filtering (ALT+D+F+F) S.N. There are 546 rows of data that should not be present in the table.

I selected the other two columns (CTRL+SHIFT+ARROWS: shortcut for selection) > ALT+ ‘;’ (selects only those individual rows containing unwanted data) > CTRL+ ‘-‘(deletes all those rows).

07

On clearing the filter we have only those data which is required and the last row is on row no. 28069.

08

Here I have used basic Excel tricks and shortcuts. Hope you liked reading it. If you know another way to solve this please do write it in the comment section.

If you have any experience of playing with Excel, please share it with us. We would love to know about it.

Thank you.

Contributor : Team Leveraged Growth

Share
1

Related posts

December 9, 2021

SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW


Read more
November 25, 2021

Circular Referencing Solution


Read more
November 18, 2021

Printing tricks


Read more

Comments are closed.

✕

Categories

  • Google
  • MS Excel
  • MS Word
  • Power BI
  • Uncategorized
  • Stacking in EXCEL
    December 16, 2021
  • SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
    December 9, 2021
  • Tips and Tricks of MS PowerPoint
    December 3, 2021
  • Circular Referencing Solution
    November 25, 2021
  • Printing tricks
    November 18, 2021
    Disclaimer Policy | Terms of Service
    Powered by Aswini Aswini Bajaj
    Website Designed & Developed by 'A PIXEL HOUSE'