Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with a column/row transposing problem
Hello, I have been puzzling over this for some time now and am sure a
seasoned excel person will have a solution. ----------------------------------------------------------------- First let me explain the spreadsheet. ----------------------------------------------------------------- It is a list of customers, and purchases that each customer has made. every purchase has a unique row. for example: - CUSTOMER ITEM customer a someitem customer a someitem2 customer a someitem3 customer b someitem4 ------------------------------------------- What I want to end up with ------------------------------------------- customer a someitem someitem2 someitem3 customer b someitem4 etc ... the problem is that the number of purchases varies between customers, some have fifteen or so whilst some have three so i'm having trouble figuring out a way of automating it. I've simplified the example but in reality there are about 10 columns which belong to each purchase, and these are what I need to append to the end of the customer row. I hope someone can help because there are over 4 thousand rows - so to do this manually is going to take me a week of sundays Thanks, Gary. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with a column/row transposing problem
I think I can help you but I need a better example -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=564646 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with a column/row transposing problem
Excellent presentation of the problem, made me want to solve it.
Sub Test() Dim iLastRow As Long Dim i As Long Application.ScreenUpdating = false iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 If Cells(i, "A").Value = Cells(i - 1, "A").Value Then Cells(i, "B").Resize(, 200).Copy Cells(i - 1, "C") Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello, I have been puzzling over this for some time now and am sure a seasoned excel person will have a solution. ----------------------------------------------------------------- First let me explain the spreadsheet. ----------------------------------------------------------------- It is a list of customers, and purchases that each customer has made. every purchase has a unique row. for example: - CUSTOMER ITEM customer a someitem customer a someitem2 customer a someitem3 customer b someitem4 ------------------------------------------- What I want to end up with ------------------------------------------- customer a someitem someitem2 someitem3 customer b someitem4 etc ... the problem is that the number of purchases varies between customers, some have fifteen or so whilst some have three so i'm having trouble figuring out a way of automating it. I've simplified the example but in reality there are about 10 columns which belong to each purchase, and these are what I need to append to the end of the customer row. I hope someone can help because there are over 4 thousand rows - so to do this manually is going to take me a week of sundays Thanks, Gary. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with a column/row transposing problem
Thankyou very much for your excellent code,
I have run it and it has got me excited because it nearly seems to be doing just what I need! This is what's happening... Once i've run it column-a is sorted into perfect ascending order and all duplicate entries have been moved. However the data that was moved appears to have been moved to the wrong place. As I read across the row I can see that some data has been overwritten in some columns and replaced with the data that we have just moved. It's as if the data we moved is being pasted over the top of the existing columns and not into it's own new columns? I hope this makes sense and you can get a feel for what's happening. I know very little code, and so simply copy and pasted your macro and can't beleive that it's nearly working! I really appreciate your time. Thanks, Gary. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with a column/row transposing problem
Gary,
It should be moving into new columns. I have assumed that columns C on are empty to start with, is this a correct assumption? Can you give an example of what it does and what it should do? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thankyou very much for your excellent code, I have run it and it has got me excited because it nearly seems to be doing just what I need! This is what's happening... Once i've run it column-a is sorted into perfect ascending order and all duplicate entries have been moved. However the data that was moved appears to have been moved to the wrong place. As I read across the row I can see that some data has been overwritten in some columns and replaced with the data that we have just moved. It's as if the data we moved is being pasted over the top of the existing columns and not into it's own new columns? I hope this makes sense and you can get a feel for what's happening. I know very little code, and so simply copy and pasted your macro and can't beleive that it's nearly working! I really appreciate your time. Thanks, Gary. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
not enough columns
Bob,
Thanks again for your reply - I realised now the part of the macro that referred to column C. Column C was not empty the first empty column was AS - so I modified the macro and replaced C with AS. It is working but I have hit another problem! It seems to have run out of columns at the end of the sheet, as those customers that have a lot of products have had the data which relates to the last product they purchased truncated from the end of their row. Suspecting it was because there wasn't enough columns, I went to the end of a row and went to insert some columns - but the insert column option is greyed out! It does appear that excel has run out of columns. I have attempted to find an answer on the internet but haven't been able to, hope you have a suggestion! And thanks again for your brilliance in getting me the code to do what I needed in such quick time. Many Thanks Gary. Bob Phillips wrote: Gary, It should be moving into new columns. I have assumed that columns C on are empty to start with, is this a correct assumption? Can you give an example of what it does and what it should do? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thankyou very much for your excellent code, I have run it and it has got me excited because it nearly seems to be doing just what I need! This is what's happening... Once i've run it column-a is sorted into perfect ascending order and all duplicate entries have been moved. However the data that was moved appears to have been moved to the wrong place. As I read across the row I can see that some data has been overwritten in some columns and replaced with the data that we have just moved. It's as if the data we moved is being pasted over the top of the existing columns and not into it's own new columns? I hope this makes sense and you can get a feel for what's happening. I know very little code, and so simply copy and pasted your macro and can't beleive that it's nearly working! I really appreciate your time. Thanks, Gary. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
not enough columns
Do you have the key in column A, and then data in B-AR? And if so, does that
mean that B-AR should be copied to the previous row on duplicates? And if yet so, what if there is too much to go in the 256 columns Excel currently has. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Bob, Thanks again for your reply - I realised now the part of the macro that referred to column C. Column C was not empty the first empty column was AS - so I modified the macro and replaced C with AS. It is working but I have hit another problem! It seems to have run out of columns at the end of the sheet, as those customers that have a lot of products have had the data which relates to the last product they purchased truncated from the end of their row. Suspecting it was because there wasn't enough columns, I went to the end of a row and went to insert some columns - but the insert column option is greyed out! It does appear that excel has run out of columns. I have attempted to find an answer on the internet but haven't been able to, hope you have a suggestion! And thanks again for your brilliance in getting me the code to do what I needed in such quick time. Many Thanks Gary. Bob Phillips wrote: Gary, It should be moving into new columns. I have assumed that columns C on are empty to start with, is this a correct assumption? Can you give an example of what it does and what it should do? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thankyou very much for your excellent code, I have run it and it has got me excited because it nearly seems to be doing just what I need! This is what's happening... Once i've run it column-a is sorted into perfect ascending order and all duplicate entries have been moved. However the data that was moved appears to have been moved to the wrong place. As I read across the row I can see that some data has been overwritten in some columns and replaced with the data that we have just moved. It's as if the data we moved is being pasted over the top of the existing columns and not into it's own new columns? I hope this makes sense and you can get a feel for what's happening. I know very little code, and so simply copy and pasted your macro and can't beleive that it's nearly working! I really appreciate your time. Thanks, Gary. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
not enough columns
Bob,
I was trying to spare some of the finer details to simplify the problem - but I can see that by doing this i've just made it more confusing, so i'll explain exactly what i'm trying to do and hopefully it will put this in context. Thank you for getting me this far i'm miles ahead of where I would have been had you not been kind enough to offer your expert assistence in the first place. .... I am using a CRM package called ACT. I am trying to get existing data (written in a custom database) from the custom database into ACT. ACT accepts comma delimitted text files by way of import, and so this is the method I am using to import data from the bespoke database into ACT. The bespoke database has two main tables. Customers and Orders. I have contructed a query using the wizard and told it that I want information from the customers table and the orders table. It returns the data - however it produces a new row for every order that the customer has placed. The result is one large file with each row representing an individual order. For this reason each row has many bits of duplicated customer data. For example, name, address, telephone number etc... appear on every row - even if ninety consecutive rows are the same customer. Now what i'm trying to do is get this data imported into Act. But Act treats every row as a unique contact and so I need to make sure that every company only has one row associated with it in this file. This is what i'm trying to achieve by using Excel. I had thought that if i removed all the generic columns - like customer name, address etc... and just kept the key column (ID - which is column A), and those columns which contain unique order information, then perhaps it would fit. But that's not the case. Taking the frequent customer that has placed 90 orders as an example - even after having removed the generic columns from the spreadsheet i'm still left with 14 columns which contain unique order data. A simple calculation 14*90 proves that this data isn't all going to fit into one row, as i've now learnt - excel has a limit of 250 columns. So I have a dilemma the code you kindly gave me works fine. So I am tantalisingly close. But I don't have enough columns! If you need any further information please ask - I look forward to hearing your thoughts on this problem. Many Thanks, Gary. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
not enough columns
Bob just in case you read this or anyone else does with a simmilar
problem... I've discovered that i've been coming at this from the wrong angle - on reflection I think there must be a way to instruct access to produce a table in the format i'm trying to achieve - one customer per row, many orders per row. So i'll try it from the DB end! Many Thanks, Gary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transposing a column | Excel Discussion (Misc queries) | |||
Transposing a row into a column | Excel Discussion (Misc queries) | |||
Transposing a column to several rows | Excel Discussion (Misc queries) | |||
Transposing a column to several rows | Excel Worksheet Functions | |||
Transposing one column into three | Excel Programming |