Convert a single column with variables and values across many rows into Table form!

[paul2014]

Hi all,

Please can anyone help. Problem: Transform/convert a single COLUMN format INTO a 5 column TABLE format. Column format incorporates 5 variables with their respective values appearing 'below' the variable's title/header, and the values appear across any number of CONSECUTIVE (row) cells, below. - see attached excel file.

1 In the Column format, there are 5 variables, their titles/headers appear normally in the following order and format: Reference, Seller Details, Buyer Details, Location, Description.
2 In the Column format, the variable's 'title/header', for example: Seller Details WILL ALWAYS PRECEED (appear stated) BEFORE the 'value' of the variable, with the EXCEPTION of variable 'Reference'. The title/header of variable 'Reference' does not appear but its value ALWAYS begins with 'R/'.
3 Values for all variable have no maximum number of characters. The values for the variable may, appear across several lines WITHIN the cell, be a mixture of upper and lower case and alpha-numeric characters, continue into the CONSECUTIVE cell BELOW, sometimes exhibits no values, meaning no rows, ONLY the 'title/header'.
4 For the Table format, a comma is required denoting the END of each cell's value for all variables. Any existing comma in the Column Format must show in the Table form.
5 Variables in this SAMPLE have been colour coded to assist interpretation in this exercise

Please see attached in excel 2010 and excel 2003 format.


Thanks in advance

COLUMN FORMAT (dummy data):
Variables
R/9742
Seller Details
Buyer Details
Mr Dave Johnson
352 Parkside London, E2 5GD, tel: 07846761
Location
davej90210@msn.co.uk
Description
Immaculately maintained with huge potential for serious HEALTH
R/974235
Seller Details
Mr James Dalton
Jdalton@jamesdw.co.uk
Buyer Details
Mr Andrew Baker
Health Unit 10 High Street Kingston KT3 7AA
Location
10 & 12 High Street Kingston KT4 3AJ
Description
Health Spa model 35 with 5kw power for decommissioning
R/454
Seller Details
PA
49 Circus Place Birmingham B8 6DH
0121 48 312 Pahealth@yahoo.co.uk
Buyer Details
Sally Goddard
216 Martindale Gardens London SE8 5SZ
Location
please call health service
Description
Scanners to monitor large abnormalities associated with nephrologist
R/97423546
Seller Details
Medical instruments, Mr JD Taylor
11 Chadwell Road, Essex RM1 4RF
JDTMedical@yahoo.com
Buyer Details
Mr PSA
88 Radcliff Road Middlesex UB12 9AJ
Location
The Walks. London,
Description
Multi bone density lateral teeth graft
R/948846
Seller Details
Ms A Walker
67 Kent Road Kent
Buyer Details
B Collins
90 Streatfield Gardens Birmingham B3 9BS
Location
90 Streatfield Gardens Birmingham B3 9BS
Description
Blood pressure and cholestrol monitor. Treadmill and physiotherapist ball

[p24leclerc]

it is not the best program but it works.
You have to move your original data in column A. See attached workbook for modifications.

Please Login or Register  to view this content.

[MickG]

Try this:-
Code Based on Data in column "C" starting "C20" results sheet1 starting "A1".

Please Login or Register  to view this content.

Regards Mick

[paul2014]

Hi Pierre Leclerc,
Thank you for your code for the provided sample of 55 rows (lines) from cell A20.
However, this code does not work for the population of 50,000 rows following the same format. Please note, the 55 rows sample data was colour highlighted to assist as oppose to the 50,000 rows/lines population is not colour highlighted. - hence the code should avoid making reference to the colour highlighting. After I have paste valued 50,000 rows from cell A20 (only in column A), the code does not allocate the values of the variables under the correct title/headings in your attached file, however allocation remains within column C and G. I am a novice to VBA, and would sincerely appreciate any help.
Thanks in advance.
Kind regards,
Paul

[paul2014]

Hi Mick,
Thank you for your code for the provided sample of 55 rows (lines).
I click run and the following box appears:
-----------------
Run-time error '9':
Subscript out of range

End Debug
-----------------
I have clicked on 'Debug' and the following code appeared in yellow (per attached):
Sheets("Sheet1").Range("A1:E1").Value = Array("Description", "Seller Details", "Buyer Details", "Location", "Description")

think 'Description' has been duplicated, the first/beginning should be variable 'Reference' which is not stated, instead identifiable by reference Ids: "R/"

will the code work for 50,000 rows in the same format, - Sincere thanks in advance for any help for this novice!

Kind regards,
Paul

[p24leclerc]

I don't know what went wrong with my macro as I tested it with 47500 rows and it worked very well. It also perform a lot doing all the processes in about 3.3 seconds. The cells were not color coded and the macro do not look at the color at all.

By the way, Mick's solution takes a lot more time. I tested it with only 5500 rows and it took about 23 seconds.

I know my computer is an old one. It is slow but it is for both macros though.