SOLVED

Horizontal Dataset to Vertical Dataset

%3CLINGO-SUB%20id%3D%22lingo-sub-2798006%22%20slang%3D%22en-US%22%3EHorizontal%20Dataset%20to%20Vertical%20Dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798006%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20issue%20about%20transforming%20%3CSTRONG%3Echeck-in%20dataset%20(Horizontal)%26nbsp%3B%3C%2FSTRONG%3Eto%26nbsp%3B%3CSTRONG%3EBD%20dataset%20(Vertical)%26nbsp%3B%3C%2FSTRONG%3Eas%20you%20can%20see%20in%20pic%201%20and%20pic%202.%3C%2FP%3E%3CP%3EIs%20there%20a%20solution%20without%20macro%20to%20solve%20this%20issue%2C%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20I%20used%20to%20do%20macro%2C%20that%20way%20I%20need%20to%2C%20because%20it's%202360%20registed.%3C%2FP%3E%3CP%3E-%20Currently%2C%20I%20used%20manual%20vlookup%20as%20you%20can%20see%20in%20pic%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2798006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-2798069%22%20slang%3D%22en-US%22%3ERe%3A%20Horizontal%20Dataset%20to%20Vertical%20Dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1168693%22%20target%3D%22_blank%22%3E%40james0862%3C%2FA%3E%26nbsp%3BCan%20be%20done%20fairly%20easily%20with%20Power%20Query%2C%20a%20built-in%20Excel%20feature.%20Not%20for%20the%20Mac%20though!%3C%2FP%3E%3CP%3ENo%20VBA%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798075%22%20slang%3D%22en-US%22%3ERe%3A%20Horizontal%20Dataset%20to%20Vertical%20Dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798075%22%20slang%3D%22en-US%22%3EIs%20there%20a%20proper%20key%20word%20used%20for%20this%20issue%20for%20googling%3F%3C%2FLINGO-BODY%3E
New Contributor

I have an issue about transforming Check-in dataset (Horizontal) to DB dataset (Vertical) as you can see in pic 1 and pic 2.

Is there a solution without macro to solve this issue, please?

 

- I used to do macro, that way I need to, because it's 2360 registed.

- Currently, I used manual vlookup as you can see in pic 2.

    (1st column as lookup_value, 2nd column as index.)

4 Replies

@james0862 Can be done fairly easily with Power Query, a built-in Excel feature. Not for the Mac though!

No VBA needed.

Is there a proper key word used for this issue for googling?
best response confirmed by james0862 (New Contributor)
Solution

@james0862 Attached a small example. A good starting point would be the site in the link below.

https://exceloffthegrid.com/power-query-introduction/ 

....and in particular, chapter 13 "Unpivoting data".

Thank you a lot @Riny_van_Eekelen, this help me lots.
I have search for this topic for 2 days.