Tranpose

Hi Excel Experts,

I want to transpose columns to rows.

I have a data in Sheet1 as below:

Akin 79

Akin 106

Akin 134

Akin 121

Arnett 477

Arnett 423

Arnett186

Arnett 129

Arnett 562


And I need to get data arranged in the following way, i.e. the values on the second column to be displayed in a single row against their respective unique name.

Akin 79 106 134 121

Arnett 477 423 186 129 562

THNKS IN ADVANCE

Hi Pankaj,


See the attached file where I have used just formulas to achieve the desired result.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi pankajsc96 and welcome to the forum,

I have a different solution than the Array function suggested by dilipandey. Instead, I've used two helper columns with Index and Match to solve your problem. See if this one makes more sense.

Hi MarvinP
Thank you very much
pls chk my actual file which need I to process quite often...
I know nothing about XL except vlookup's & count's tht's it

Thanks in advance

OK,

See it here with your data...

Hi pankajsc96,

300,000 rows is a lot!! I'm wondering if you have enough power and/or memory in that machine to not hang on such a big file. I could write some VBA to accomplish the same transpose but can't insure it would be any faster or not hang like you are getting now. Perhaps if you could explain the real problem in more detail a Pivot Table or other method would be a better solution for your needs.

Hi,
I don't mind the english. I'm wondering why my method hangs. I too have a power machine with a fast cpu and lots of memory.

I'm wondering why you need the data like you suggest. If you did an AutoFilter with your columns A and B you could filter the source and see a list or Targets very easily. My question was more of "what question are you trying to answer" by wanting to see the "transposed" data like you suggest? I'm thinking an Autofilter or Advanced Filter or even Pivot Table may be all you need to answer your questions. You felt a transposed view was your answer but I'm thinking there are other ways. That is why I asked if you could explain the real problem in more detail above.

Also, what is the real data like? Are there only a few Targets for each Source? How many different Sources are there in a set of 300K rows?

Hi paknaj,

Is the real question is to find those cell towers that drop a call? If it is then how does putting the data in your transposed format show that?
Is the question to count the number of tower handoffs that are successful?
Is the question to see how many 2G/3G/4G handoffs work correctly if the number in front of the G changes?

I'm thinking that your transposed format of the data makes Excel work harder. Excel loves TABLES of data in the original format. I'm thinking you can get a Autofilter or Advanced Filter or even Pivot Table to do all the work, faster and easier. I'm just looking for the REAL question you are trying to answer.

See the attached for 3 simple (maybe not that simple) Pivot Tables using your example data.

Using Pivot Tables' Sorts, Filters, Groups and Expand/Collapse features; I think you can do lots more than the "transposed" format you want to move your data into. AND Pivot Tables are FAST!!

I'm sorry but the attached file above seems to be password protected and I can't get into it.

Did you try AutoFilter on your data? It should be very fast. I guess I need a bigger file and exactly why you need it in the format you suggest to see if I can help.