Excel 2007 : How to convert data from vertical to horizontal

[REHANSRSPL]

DEAR
stanleydgromjr


THANK U VERY MUCH FOR YOUR VALUEABLE SUPPORT.

[arlu1201]

Is 00~~ the identifier to differentiate between one data section and the other?

[REHANSRSPL]

is 00~~ the identifier to differentiate between one data section and the other?

yes

group range start wtih 00~~ line
and with 19~~ line

exmp.

00~~rehan
02~~city
03~~etc
19~~india

in somedata it is verticaly 07 raw or sometime 05 rows that is my problem to transpose thie

thanks in adv.

[stanleydgromjr]

REHANSRSPL,

Thanks for the workbook.


The macro will run on the first worksheet (left most worksheet in the tabs view), and, it will create a new worksheet Results with the results you are looking for.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

[REHANSRSPL]

Re: Excel 2007 : How to convert data from vertical to horizontal


I HAVE ONE MORE PROBLEM IN THIS

TOTAL DATA IS

00
01
02
03
04
05
06
07
08
12
16
17
18
19

WHEN SOME RECORD DOES NOT HAVE DATA OF 02 THEN MACRO WILL PASTE 03 DATA IN 02 COLOUMN THAT IS THE PROBlem

i have attach file with u r macro and my problem.

thank in adv


I WANT THIS WILL PASTE 01 DATA IN 01 COLUMN AND IF REOCRD DOES NOTE HAVE 01 DETAIL THEN IT WILL BLANK COUMN AND JUMP TO NEXT FIELD

00
01

FILE ATTACH

[stanleydgromjr]

REHANSRSPL,

Your raw data worksheet contains data in just column A like this:

00
01
02
03
04
05
06
07
08
12
16
17
18
19


1. The Results worksheet should have 14 columns like this, with all the 00's in column 00, and all 02's in column 02, and all 19's in column 19, etc., etc...:

00, 01, 02, 03, 04, 05, 06, 07, 08, 12, 16, 17, 18, 19


2. But, your next two statements contradict each other:

I WANT THIS WILL PASTE 01 DATA IN 01 COLUMN AND IF REOCRD DOES NOTE HAVE 01 DETAIL THEN IT WILL BLANK COUMN AND JUMP TO NEXT FIELD
00
01

WHEN SOME RECORD DOES NOT HAVE DATA OF 02 THEN MACRO WILL PASTE 03 DATA IN 02 COLOUMN THAT IS THE PROBlem


What is the output that you are looking for, 1. or 2.

[stanleydgromjr]

REHANSRSPL,

I had to adjust the Results worksheet column widths, to a manageable width, in order to see what your results should look like.


OK, the next macro will produce the results your are looking for, based on your last posted workbook, worksheet Results.


See the BOLD notes in the macro:
' if you want the Results worksheet columns to be expanded to their correct width
' then on the next line of code remove the leading ' characters.
' and these three lines of text
''''.Cells.EntireColumn.AutoFit


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.

[REHANSRSPL]

What i want i explain in final sheet

[REHANSRSPL]

i want this
00 data in a
01 data in b
02 data in c
03 data in d
04 data in e
05 data in f
06 data in g
07 data in h
08 data in i
12 data in j
16 data in k
17 data in L
18 data in m
19 data in n

if above detail such as 00 to 19 have no record then macro will skip their specific column blank
like if data has no detail start with 03~~ then macro will skip d column.


your current macro will super but
it will paste data 03 data in c coulnm if 02 data is missing.



thanks in adv

[xlbiznes]

Hi,

Hope this code will help.

Copy the code onto your "RAW DATA" sheet and run the macro. You also need to create a sheet called "FINAL DATA" , which would contain your organized data.

Happy Computing.

Please Login or Register  to view this content.

[stanleydgromjr]

REHANSRSPL,

Thanks for the latest instructions.

With your raw data in the first worksheet, the left-most-worksheet in the tabs view.

The following macro (just like my previous 2 macros) is very fast because of the use of arrays in memory.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV3 macro.

[stanleydgromjr]

REHANSRSPL,


With your raw data in the first worksheet, the left-most-worksheet in the tabs view.


The below ReorgDataV4 macro is shorter in code, and, it is faster then macro ReorgDataV3.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV4 macro.

[jindon]

Not sure if the attached is what you want,

If not, just ignore it.

[REHANSRSPL]

Please see the attachment

it willl give error

[REHANSRSPL]

Thanks for all your great help - I simply couldn't have done it without you AND THE GOD !!



HAVE A GREAT DAY.

[REHANSRSPL]

Stan
stanleydgromjr

Thanks for all your great help - I simply couldn't have done it without you AND THE GOD !!



HAVE A GREAT DAY.

[xlbiznes]

Hi,

You have pasted the code inside the module , that is the reason it is giving you an error.

You need to paste this code inside the sheet code which contains your raw data.

Hope this helps.

Happy Computing.

[stanleydgromjr]

REHANSRSPL,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.

[REHANSRSPL]

THANKS FOR U HELP,
ONE MORE THING I WANT IN THIS MACRO, BCOZ THIS DATA HAS BEEN USED TO CHECK DETAIL OF USER ACCOUNTS, when I was process the data I am facing the problem of blank cells
In addition of the macro
IF U CAN REPLACE RESULT SHEET BLANK CELLS DATA WITH ~ SIGN AS PER BELOW
IF COLUMN C HAS NO DATA THEN PUT ~ SIGN 32 TIMES
IF COLUMN D HAS NO DATA THEN PUT ~ SIGN 32 TIMES
IF COLUMN E HAS NO DATA THEN PUT ~ SIGN 12 TIMES
IF COLUMN F HAS NO DATA THEN PUT ~ SIGN 10 TIMES
IF COLUMN G HAS NO DATA THEN PUT ~ SIGN 40 TIMES
IF COLUMN H HAS NO DATA THEN PUT ~ SIGN 40 TIMES
IF COLUMN I HAS NO DATA THEN PUT ~ SIGN 40 TIMES
IF COLUMN J HAS NO DATA THEN PUT ~ SIGN 11 TIMES
IF COLUMN K HAS NO DATA THEN PUT ~ SIGN 6 TIMES
IF COLUMN L HAS NO DATA THEN PUT ~ SIGN 7 TIMES
IF COLUMN M HAS NO DATA THEN PUT ~ SIGN 4 TIMES
IF COLUMN N HAS NO DATA THEN PUT ~ SIGN 3 TIMES

It will help me.
Thanks in advance.

[stanleydgromjr]

REHANSRSPL,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV5 macro.

[REHANSRSPL]

It will fill ~ in blank cell great

but it will also fill the range as per sheet(1) means if sheet1 has 16481 range
it will replace result sheet blank cell to 16481.

I want in result sheet it will reaplce ~ in (02,03,04,05,all) only where "a" colunm fill with data


please see the attachment.

Thanks u

[stanleydgromjr]

REHANSRSPL,

In your most current workbook you have:

In Module1

Please Login or Register  to view this content.

In Module2

Please Login or Register  to view this content.

In order for my most current macro to work correctly, the macro code must contain the leading two lines of code:
In Module2

Please Login or Register  to view this content.

Macro ReorgDataV5 will work correctly in your posted workbook if you follow the above instructions.

[REHANSRSPL]

Ok

but as per your instruction

i will do the same but problem exist.

It will paste ~ in extra line .

[stanleydgromjr]

REHANSRSPL,

If you are not satisfied with what I have done, then:

In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

[REHANSRSPL]

i am extremly sorry for that which i am done to you

you are awsome in u r work.

this is only my mistake that i want more and more editing in this.


please dont anger on my

and forgive as your younger brother


MATTER SOLVED.

Sorry for inconvenience caused to you.




your younger brother
REHAN