Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed extracting data accross the row into the first cell of the row
What i'm trying to achieve is a little tricky to explain so i've tried
to be as thorough as possible! If you want any more information please ask! I'm depending on your responses, thankyou. The Worksheet Setup =========================== Each row contains a unique customer record. As well as customer contact information, customer order-history is contained within each row. A (simplified) layout of the worksheet is: - ------------------------------------------------------------------------------------------------------------------------ Name Address Tel Make1 Qty1 Price1 Makel2 Qty2 Price2 --- Name2 Adress2 Tel2 etc.. --- ..... ------------------------------------------------------------------------------------------------------------------------ Some customers have ordered 90 products and so any given row may contain hundreds of columns. What I Need =========================== I need for each customer (every row) to create an order-history cell, which combines all of the product order details from the current row. The history cell is basically a string formatted so that every order appears on it's own line. So for each customer I want something like this in the first cell of their row. (please excuse lack of artistic talent.) -------------------- A ------------------------ | Order1 Price1 Qty1 | | 1 | Order2 Price2 Qty2 | | | Order3 Price3 Qty3 ------------------------------------------------- What I have so far =========================== Using the fact that each piece of order information repeats itself every 43 columns I have been able to make a start. For example the first 'Make' column (these orders are for cars) appears in the 7th column. For this reason I know that every 7+43rd column contains model information. The following code takes the contents of the seventh, and subsequent seventh columns of the current row (i.e. all the model data) and puts them into the first column (A) of the current row. I have added CR's using Chr(10) so that each column is placed onto a new line as it is placed in the text string. this is the code: - ---------------------------------------------------------------------------------------- 'setup the variable dim lastcolumn as long dim makecolumns as long dim orderhistory as string 'initialise lastcolumn lastcolumn = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column For makecolumns = 7 To lastcolumn Step 42 orderhistory = orderhistory & Chr(10) &(Cells(ActiveCell.Row, makecolumns).Value) Next makecolumns Cells(ActiveCell.Row, "A") = textstring ----------------------------------------------------------------------------------------- This produces a result something like the following: - ------------------------------ Ford Vauxhall Audi .... ------------------------------ What I need =========================== I need to modify the above so that at each iteration not only is the make information added to the order history cell, but the quantity and price information is too. The quantity information starts at column 8 and repeats likewise every 43 columns. The Price information starts at column 9, again this repeats every 43 columns. What I want to end up with in the first cell of each row is something like this... Ford 3 £13,000 (ford taken from 7th column, 3 taken from the 8th column, 13,000 taken from the 9th columns.) Vauxhall 7 £14,000 (vauxhall from the 50th column, 7 from the 51st column, 13,000 from the 52nd column.) Audi 9 £19,000 (audi from the 93rd column, 9 from the 94th column, 19,000 from the 95th column.) .... ... ... etc... === Thanks for taking the time to read this, please suggest solutions. Gary. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed extracting data accross the row into the first cell of the row
Please excuse the type step in the code.
thanks again. Gary. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed extracting data accross the row into the first cell of the row
Also in the sample table 'price2' is part of the first row - google has
very poor formatting power - sorry. Thanks, Gary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Data From The Right of a Cell | Excel Worksheet Functions | |||
I need help extracting some data from a cell | Excel Worksheet Functions | |||
Extracting data from a cell | Excel Worksheet Functions | |||
Extracting data from a cell | Excel Discussion (Misc queries) | |||
extracting data from a cell | Excel Worksheet Functions |