Avatar of ADRIANA P
ADRIANA P
Flag for United States of America asked on

NEED TRANSFER DATA

HAVE A COLUMN WITH DATA  NEED TO BE REORGANIZED

THIS WAY
CHANGE-FOR-ANALYSIS.xlsx
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

The attached assumes that you want the black cells and that the output goes into the columns you said (M, N and O) and not the ones that you show (N, O and P).

To see the results, go to Sheet1 and press Ctrl+Shift+S
29011545.xlsm
ADRIANA P

ASKER
MARTIN

CAN BE A NEW  SHEET TOO

LET'S SAY  I CAN PASTE THE DATA
IN A NEW WOORKBOOK WITH AN SHEET A  COLUMN A  P LASTE THERE
AND A NEW SHEET B CAN BE THE RESULTS
ADRIANA P

ASKER
excuse me about the caps on
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Subodh Tiwari (Neeraj)

You may try something like this...
Click the button on Sheet1 to run the below macro to get the desired output on a separate sheet called Output. This sheet will be inserted by the macro if not present in the workbook.

Sub TransposeData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, c As Long
Dim Rng As Range

Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
slr = sws.Cells(Rows.Count, 5).End(xlUp).Row
On Error Resume Next
Set dws = Sheets("Output")
dws.Cells.Clear
On Error GoTo 0
If dws Is Nothing Then
    Sheets.Add(after:=sws).Name = "Output"
    Set dws = ActiveSheet
End If
For Each Rng In sws.Range("E7:E" & slr).SpecialCells(xlCellTypeConstants, 2).Areas
    c = c + 1
    dws.Cells(1, c).Value = Rng.Cells(1).Offset(-1, -1).Value
    Rng.Offset(-1, 0).Resize(Rng.Cells.Count + 1, 1).Copy dws.Cells(2, c)
Next Rng
dws.Rows(1).HorizontalAlignment = xlCenter
Application.ScreenUpdating = True
End Sub

Open in new window

CHANGE-FOR-ANALYSIS.xlsm
Martin Liss

Try my workbook and let me know what needs changing.
Martin Liss

Make sure you are looking at Sheet1 and then press and hold the Control and Shit keys and press the "S" key. Then look at sheet2.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

This version has a "Transfer" button.
29011545a.xlsm
ADRIANA P

ASKER
MARTIN IS ALMOST DONE
BUT
 IS EVERY 12  RANGE
1 IS BLANK
2 TO 13 IS DATA
AND BEGING AGAIN
1 IS BLANK
2 TO 13 IS DATA
ADRIANA P

ASKER
Subodh Tiwari (Neeraj)  for you promplty answer

almost work but i ahve over 3000 records
and anfert column 10 phase out the data

have to be  in ranges of 13
1 blank
2
to
13 data
and beging again
1 blank
2  to 13 data
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Martin Liss

My results are exactly what you show in the example (except for the columns).
Subodh Tiwari (Neeraj)

Hi Adriana,

Do you want to have max 10 columns of transposed data on one sheet and then start over again transposing the data on the new sheet?
ADRIANA P

ASKER
maritn this is what happens
trans.png
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
Subodh Tiwari (Neeraj)

lets see...
Subodh Tiwari (Neeraj)

Still hard to guess your end result.
BTW try this to see if this is what you are trying to achieve here.
Sub TransposeData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, c As Long, i As Long
Dim Rng As Range

Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
slr = sws.Cells(Rows.Count, 5).End(xlUp).Row
c = 1
i = 1
For Each Rng In sws.Range("E7:E" & slr).SpecialCells(xlCellTypeConstants, 2).Areas
    If c = 1 Then
        On Error Resume Next
        Set dws = Sheets("Output" & i)
        dws.Cells.Clear
        On Error GoTo 0
        If dws Is Nothing Then
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Output" & i
            Set dws = ActiveSheet
        End If
    End If
    dws.Cells(1, c).Value = Rng.Cells(1).Offset(-1, -1).Value
    Rng.Offset(-1, 0).Resize(Rng.Cells.Count + 1, 1).Copy dws.Cells(2, c)
    c = c + 1
    If c > 10 Then
        c = 1
        i = i + 1
        Set dws = Nothing
    End If
Next Rng
Application.ScreenUpdating = True
End Sub

Open in new window

Subodh Tiwari (Neeraj)

If you want column A to be blank on all the new sheets, change all the occurrences of c = 1 with c = 2.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ADRIANA P

ASKER
this is  what have to be
trans233.png
Subodh Tiwari (Neeraj)

Can you upload a sample workbook with the desired output mocked up manually as the images you have uploaded so far don't help much as we cannot see the row numbers  and column letters so not sure about the exact position of data on the sheet?
Martin Liss

When I click the "Transfer" button I get this on sheet2 which looks like the image you posted.
My result
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
martin  the file you post  to me don't works like that
can you verify please !!
 will be great if work like that

here the data i need to work on  if help to get the result
DATA-ON.xlsx
ADRIANA P

ASKER
Subodh Tiwari (Neeraj)

this is what aiget with the code you send me

it don't put the data  on teh ranges  of 13
transpo-c.png
ADRIANA P

ASKER
here what's  should comes out  please verify
rigth-data.png
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Adrianna, the data on the left side of that picture does not agree with the data on the right side.

In the workbook you posted, please tell me what rows should be in column "M" and what rows should be in column "N".
Subodh Tiwari (Neeraj)

Well it is still not clear.
Anyways find the attached and click the button to run the code.
And if this is not your desired end result, change the output on the newly added sheets and upload the workbook here.
DATA-ON.xlsm
ADRIANA P

ASKER
Subodh Tiwari (Neeraj)

now i get a lot of diferrents sheets with outputs

i need just one sheet output but
the data from one column should be put in 13 rows format all the way along
aloaoutput.png
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

Can you mock up the desired output based on your sample file and show us what do you need exactly?
Martin Liss

If you want to show us a picture the best thing to do is to click this button, click "choose file", select the file, give it a description, and click "Embed".
pic
ADRIANA P

ASKER
ok i m learning
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ADRIANA P

ASKER
i need this
Martin Liss

The data under "1" in the left hand side (blank, 824, 705, etc.) does not agree with the data you show under "1" on the right hand side (blank, 417, 279, etc.)
Subodh Tiwari (Neeraj)

Okay can you check the attached and confirm?
DATA-ON.xlsm
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
Matin i see sorry
you rigth on that
 excus me for the confusion


but like i say  one column data
to be in 13 ranges with the first blank  2 to 13 with the data and all over
Subodh Tiwari (Neeraj)

What about this?
DATA-ON.xlsm
Martin Liss

first blank  2 to 13 with the data and all over
I know that the first cell in each set will be blank, but will the 2 to 13 part ever have blank cells. If it does do you want to see the blank cells on the right?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ADRIANA P

ASKER
ok  good question !
martin
yes when one blank shoud be there too with not data in but in the same space

13 set
1 blank
2
to
13 with the data

is equal to say 12 data column

but the 1 blank
pull apart the data in the short column
ADRIANA P

ASKER
iput inthe same place i mean
Subodh Tiwari (Neeraj)

Did you check the file I uploaded?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
Subodh Tiwari

almost work but
 is taking 13
remember 13 is with first blank and 12 with data
 
1=blank
2
to
13  with data
is equal to 12 data but first blank to separate
Martin Liss

I'm sorry but I don't understand what you said. Given this data

824
705
750
232
316
949
240
377
251
719
548
841

065
744
023
857
990
310
530
666
099
822

958
226
179
623
720
748
828
421
746
140
what should be in column "M" and what should be in column "N"?
Subodh Tiwari (Neeraj)

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ADRIANA P

ASKER
matin

don't have to be in column m or n
ADRIANA P

ASKER
Martin
could be an blank workbook so i can paste there  from the original!

you can put set it so i can use coumn A
Martin Liss

I don't care what the output columns are. What should be in the first output column and what should be in the second output column?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
you can set it so i can move the data when is ready
Martin Liss

I don't care what the output columns are. What should be in the first output column and what should be in the second output column?
Please answer that question using this data which is from your workbook.
824
705
750
232
316
949
240
377
251
719
548
841

065
744
023
857
990
310
530
666
099
822

958
226
179
623
720
748
828
421
746
140
ADRIANA P

ASKER
Ok Martin Great !

824  should be in first column    065 second column   958 third column
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Shums Faruk

Two Great Experts here, and still taking so long? LOL
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk

Martin Sir,

I have different approach, If you can play around with my code to make it Column wise.
DATA-ON_v3.xlsm
ADRIANA P

ASKER
The best !
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P

ASKER
Martin ! Great Expert !

thats what i need !
Martin Liss

Give me a minute and I think I can speed it up.
Shums Faruk

Martin Sir,

Still its not perfect. Please check again.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shums Faruk

There are many columns which are not pulling right data.
ADRIANA P

ASKER
Subodh Tiwari you was so  close may be  don't have time  but very good too !
thanks  to you too!
ADRIANA P

ASKER
yes is not perfect ! but very closed ! to be
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

This takes about 8 seconds.
29011545c.xlsm
ADRIANA P

ASKER
thge blank data is because have some empty fields in the  12 cycle
there are 12  but the first is the begining of the next
ADRIANA P

ASKER
if youcan speed an d make better will be nice !
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Please see the 29011545c version I posted above.