0

I'm looking to transpose rows to columns, I tried with Transpose(Array) but could not achieve expected results. Here is the sample data and expected results I'm looking out.

Sample Data:

Number  Name    Value
1001    description Unknown
1001    Code    0
1001    GNumber 232323
1001    DNumber 232323
1002    description Unknown
1002    GNumber 232323
1002    DNumber 232323
1003    description Unknown
1003    Code    0
1003    GNumber 232323
1003    ICode   123
1004    description Unknown
1004    GNumber 232323
1004    ExtCode 4000

Expected Results:

Number  description Code    GNumber DNumber ICode   ExtCode
1001    Unknown 0   232323  232323      
1002    Unknown     232323  232323      
1003    Unknown 0   232323      123 
1004    Unknown     232323          4000

Thank you,

Improve this question
1
  • 1
    This is not really as transposing method. I think you have to use a VBA routine or a Pivot table to achieve your goal. Commented May 15, 2019 at 7:50

2 Answers 2

Reset to default
2

TDLR : create the table column & row header manually. then load values using an index() match() function inside.

Method : Assuming the 'Number' text is in A1, and your data fills in A2:C15.

  1. [create the table column & row header ]

select A2:A15 > copy > then paste in another empty sheet/file > paste > then select "remove duplicate" > copy the result > then paste in cell E2:E5 (same sheet as original data table.)

Do the same for column B. but need to transpose it. :

select B2:B15 > copy > then paste in another sheet/file > paste > then select "remove duplicate" > copy the result > then r-click on F1 > paste special : transpose .

Now you should have the table column & row header.

  1. [use 2 criteria index match (reference link) + iferror (to display null if not found). ]

put this in F2, then drag until K5 .

=IFERROR(INDEX($C$2:$C$15,MATCH(1,INDEX(($E2=$A$2:$A$15)*(F$1=$B$2:$B$15),0,1),0)),"")

Done.

p/s : to understand how the index match work.. just need to start a read on simple example of index() match().. the formula here are the same but with additional tricks.

hope it helps. ( :

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Just to add up to this solution, a small improvement might be to use a formula in order to remove duplicates, e.g. if your original data is in cells A1:C15 and your "results" table headers (Number -> ... -> ExtCode) is in cells G1:M1, then you can use the following array formula (Ctrl+Shift+Enter) in cell G2 to return unique values: =IFERROR(INDEX($A$1:$A$15,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$15),0)),"")
Thanks for the helpful unique listing array formula.. Really appreciate it.. || p/s : I'm doing it this way coz I'm not a fan of array formula.. || Thanks for the heads up.. /(^_^)
No worries! I also like your solution, that's why I upvoted it. just wanted to add some alternative / funky formula :) take care.
Thanks @JustynaMK ( :
0

This can be done in 2 ways.

WITH STANDARD FORMULAS: In this case, you will use standard formulas, but to make this work, you need an extra column.

First of all, you need to insert an extra column at start of your data, where you are going to concatenate the values of fields NUMBER and NAME. In my image I used =B2&C2:

enter image description here

Then, to get the data, you can use a VLOOKUP that will search for the concatenated text, and if found, will get the value. We combine this with an IFERROR to show "" if nothing found.

=IFERROR(VLOOKUP($B19&C$18;$A$1:$D$15;4;FALSE);"")

And this will work, as you can see in the image above.

WITH ARRAY FORMULAS: This way is a more complex formula, but the good thing is you don't need to add an extra column.

We combine MATCH and INDEX in array mode to get the value you want, and we use an IFERROR to show "" if nothing found:

enter image description here

The formula I used is:

=IFERROR(INDEX($C$2:$C$15;MATCH($B22&C$21;$A$2:$A$15&$B$2:$B$15;0));"")

NOTE:: Because this in an array formula, it must be entered with CTRL+SHIFT+ENTER or it won't work!

I've uploaded both samples to my Gdrive in case you want to check the formulas and how do they work.

https://drive.google.com/open?id=1ke7L5W1Ii8TffR7HTPtPxnAHVwyDYymy

Hope this helps!.

Improve this answer

3 Comments

Thank you, 2nd options looks a bit helpful. Sorry I did not mentioned this earlier. But, I have around 2-3 million records and columns may vary for each record (we can say number here). Above I just gave a sample records to understand requirement which shows just 6 columns.. But in actual some record (number) may contain 50 columns, some may contain 70-80 columns. Your 2nd example shows like you have already defined columns.. Can we do this dynamically, the unique column is number in my dataset.
If your original data is always like the sample data you provided (3 columns), both options will work even if your expected output is 10,50 or 3000 columns because I've used mixed references in the formulas. The trick here is using name of column and number concatenated together. But if your original data may change and not always be 3 columns, then probably it won't help.
Great! this helps now. Thank you..!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.