Can You Solve This, By Using Transpose, Skip Blanks and Sorting Options ?

Today, I am going to explain you a Cool Trick to Transpose more than one Column Data into Single Row.

See the below case, and Try Your Own.

Did you get any Clue??


OK. Let me give you Few Clues..you can use Sort Option, Transpose Option and Skip Blanks Option

Still Not getting any Clue??

How to fill the Blank Cells?

Please follow below steps.

1. Select B5 : C14

2. Click F5 (Go To Box appears)

3. Use Alt+S,K (click Special and select Blanks)

4. Enter (Click OK)

Then Excel selects the Blank Cells ie., B6,B7,B8,B9,B11,B12,B13,B14

1. Enter =Up Arrow

2. Click Ctrl+Enter (Short Cut to enter same data into multiple selected cells)

Then Excel fills the immediate Upper Cell Data in the Blank Cells.

1. Select B5 : B14

2. Ctrl+C (Copy)

3. Right Click, S, V (Paste Special as Values)

Then all Formulas disappear and Values displays.

Now, you can able to fill the Blank Cells with the Upper Cell Value.

Am I need to Do anything before Transpose?

If you do Transpose directly after copying these two columns data, then the Transpose data appears in Two Rows but not in One Row like in above case.

So, You need to act something before use Transpose Option.

1. Enter 1 in D5

2. Enter 3 in D6

3. Auto fill by selecting D5 & D6, till D14 (Select D5 and D6, drag by holding right down edge of D6 Cell)

4. Enter 2 in D15

5. Enter 4 in D16

6. Auto fill by selecting D15 & D16, till D23 (Select D15 and D16, Drag by holding right down edge of D16 Cell)

Now use Sort Option as follows.

1. Select B5 : D23

2. Ctrl+D,S (For Sort Option)

3. Sort By "Column D" (Make sure the Order is Smallest to Largest)

4. Use Alt+H (Only if, 'My data has headers' has Checked)

5. Click Enter

Where is the Climax?

Yes, I am coming to the climax now.

In this Climax we are going to use the Transpose Option and Skill Blanks Option.

Simply follow the below steps to get the solution

1. Select B5 : B23

2. Ctrl+C (Copy)

3. Go to Cell F2 (or where you want to get the info)

4. Right Click, S, E and Enter (Paste Special as Transpose)

5. Select C5 : C23

6. Ctrl+C (Copy)

7. Go to Cell G2 (Between the first two cells in the info)

8. Right Click, S, B, E and Enter (Paste Special as Skip Blanks and Transpose)

9. That's All !!!

If you find any other alternative, please share below as comments. I Love to learn and Share !!