Transpose of Data

[MaverickSemperFi]

Hi All,

I was hoping somebody would be able to help with an issue i am having at the moment.I am trying to transpose data from one spreadsheet to another using an index formula however, i am having issues.

(Please see attached image of example spreadsheet)

What i am hoping to do is transpose the information on Figure 1 to Figure 2.

When i try to get (Figure 1) No.1 Ref A for example to Ref A No.1 on Figure 2 it works no problem. The problem that does occur however, is for No.2 - No7 as it doesnt recognise the A from figure 1 it sees it as a blank cell. I was originally going to copy them over indiviually however, there is a vast amount of information and i want to use this exact sheet for other information of a similar standard.

I would be extrmeley grateful for your help as i have tried everything i know (which is limited). If no one knows a way of solving this i have another possible solution that i also would need your help with but its very complex and a bit of a pain.

[Special-K]

Where is 255.9 stored? It looks like C3 but is it?
This is where posting the file, or a small sample of it, instead of an image helps more.

[Kaper]

As you posted no sample workbook, nor described you method to transpose data, try the following (rather universal) procedure:
Select your merged cells with A...D. Unmerge (format cells: Ctrl+1 and unmark merge cells).
Having these cells selected use Goto (Ctrl+G) and then select Special - Empty cells
Note that several ranges are now selected between cells with A and B, B and C etc. Assumming your A is in cell B5 (first selected cell = the active one is B6)
in formula bar write =B5 and press Ctrl+Enter

now in first colun you will have
A
A
A... 7 times
B
B... etc


so your transposition method shall work for all data.

[MaverickSemperFi]

Apologies for not attaching sample workbook. I have edited post to include sample workbook.

I shall give these options ago.

Many Thanks.

[Special-K]

So your data on the ACTUAL spreadsheet starts in B5 and contains the letter A ?
And the value in column B never exceeds 7 (since your example output doesnt allow for that) ?

Somehow I suspect this is not the case and this sample data you've provided is a merely an aesthetically pleasing output.
This means whatever solution is provided you will have to modify it (by yourself) to fit your actual data.

[Special-K]

This works on your sample spreadsheet, adjust to fit if necessary

Please Login or Register  to view this content.

[jindon]

Here's how I read your problem

Please Login or Register  to view this content.

[nilem]

or maybe so

Please Login or Register  to view this content.

[Special-K]

Ignore this, I unsubscribed to this thread by mistake, just replying so it resubscribes (unless there is another way to do this?)

[MaverickSemperFi]

Thanks all i will give these a try.

Much appreciated, If you think of any other ways please let me know just in case.

[63falcondude]

Ignore this, I unsubscribed to this thread by mistake, just replying so it resubscribes (unless there is another way to do this?)

FYI
Thread Tools > Subscribe to this Thread

[MaverickSemperFi]

Does anybody who provided a solution have any pseudocode or notes that i could use?

[MaverickSemperFi]

I have added a new example spreadsheet that is a more accurate representation of my spreadsheet with random data. They are also on seperate sheets. I tried to modify the code to fit my spreadsheet but didnt manage to sort it.

Really appreciate the help.

[Kaper]

This shall do (tried to write it rather simple, using built-in worksheet functions from excel 2016):

Please Login or Register  to view this content.

[MaverickSemperFi]

This shall do (tried to write it rather simple, using built-in worksheet functions from excel 2016):

Please Login or Register  to view this content.

Thank you, this helps loads. Really Appreciate it.

[Kaper]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

PS. Note rule 12 of our https://www.excelforum.com/forum-rul...rum-rules.html