Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging data
I have an Excel spreadsheet that is imported from a Online orderingsystem,
thus the format is predefined. I need to arrange the data properly in order to import theese data to Access. I use Office 2003. Each line starts with a customerID number in column A. I the first row is listed the different items that has been ordered. One item in each coumn. If the customer in line 4 has ordered the item listed in column C, the cell C4 will have the itemID of that item. The result is a spreadsheet where each customer has severeal items ordered, but each for each item that is not ordered, the corresponding cell is left blank. I need to arrange the data so that each line starts with the customerID (as now), and all the items is listed from column B onwards removing all the blanks, and disregarding the item names listed in row 1. Row 1 will be deleted before the import. Suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging data
Try looking here for an initial solution:
http://www.contextures.com/xlDataEntry02.html Debra Dalgleish's site -- Regards, Tom Ogilvy "Petterq" wrote in message ... I have an Excel spreadsheet that is imported from a Online orderingsystem, thus the format is predefined. I need to arrange the data properly in order to import theese data to Access. I use Office 2003. Each line starts with a customerID number in column A. I the first row is listed the different items that has been ordered. One item in each coumn. If the customer in line 4 has ordered the item listed in column C, the cell C4 will have the itemID of that item. The result is a spreadsheet where each customer has severeal items ordered, but each for each item that is not ordered, the corresponding cell is left blank. I need to arrange the data so that each line starts with the customerID (as now), and all the items is listed from column B onwards removing all the blanks, and disregarding the item names listed in row 1. Row 1 will be deleted before the import. Suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging data
Hi.
This is something else. On this link i find tips to fill inn blanks, but that is not the problem. I need to arrange all celles that have a value from column A and onwards through B, C and so on. As many columns needed to room all the values. The number of columns needed will vary from customer to customer. Example: CustomerID 8000 in A1 Ordered items: B1:43, D1:55, H1:63 After arranging: CustomerID 8000 in A1 Ordered items: B1:43, C1:55, D1:63 For on row only it can be done with a sort function, but then I have to sort one row at a time. I will have spreadsheets with hundreds of rows, and then it will be to time consuming to take it row by row. Any suggestions to automate the procedure? "Tom Ogilvy" wrote: Try looking here for an initial solution: http://www.contextures.com/xlDataEntry02.html Debra Dalgleish's site -- Regards, Tom Ogilvy "Petterq" wrote in message ... I have an Excel spreadsheet that is imported from a Online orderingsystem, thus the format is predefined. I need to arrange the data properly in order to import theese data to Access. I use Office 2003. Each line starts with a customerID number in column A. I the first row is listed the different items that has been ordered. One item in each coumn. If the customer in line 4 has ordered the item listed in column C, the cell C4 will have the itemID of that item. The result is a spreadsheet where each customer has severeal items ordered, but each for each item that is not ordered, the corresponding cell is left blank. I need to arrange the data so that each line starts with the customerID (as now), and all the items is listed from column B onwards removing all the blanks, and disregarding the item names listed in row 1. Row 1 will be deleted before the import. Suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging data
Sub RemoveBlanks()
Range("B:AZ").SpecialCells(xlBlanks).Delete Shift:=xlShiftLeft End Sub would be another guess. Test it on a copy of your data. -- Regards, Tom Ogilvy "Petterq" wrote in message ... Hi. This is something else. On this link i find tips to fill inn blanks, but that is not the problem. I need to arrange all celles that have a value from column A and onwards through B, C and so on. As many columns needed to room all the values. The number of columns needed will vary from customer to customer. Example: CustomerID 8000 in A1 Ordered items: B1:43, D1:55, H1:63 After arranging: CustomerID 8000 in A1 Ordered items: B1:43, C1:55, D1:63 For on row only it can be done with a sort function, but then I have to sort one row at a time. I will have spreadsheets with hundreds of rows, and then it will be to time consuming to take it row by row. Any suggestions to automate the procedure? "Tom Ogilvy" wrote: Try looking here for an initial solution: http://www.contextures.com/xlDataEntry02.html Debra Dalgleish's site -- Regards, Tom Ogilvy "Petterq" wrote in message ... I have an Excel spreadsheet that is imported from a Online orderingsystem, thus the format is predefined. I need to arrange the data properly in order to import theese data to Access. I use Office 2003. Each line starts with a customerID number in column A. I the first row is listed the different items that has been ordered. One item in each coumn. If the customer in line 4 has ordered the item listed in column C, the cell C4 will have the itemID of that item. The result is a spreadsheet where each customer has severeal items ordered, but each for each item that is not ordered, the corresponding cell is left blank. I need to arrange the data so that each line starts with the customerID (as now), and all the items is listed from column B onwards removing all the blanks, and disregarding the item names listed in row 1. Row 1 will be deleted before the import. Suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arranging data
Hello again.
This looks promissing, however I get an error message that reads something like: Run-time error '1004': The Delete-method in the Range-class was unsuccessful (translated from Norwegian) I pasted in the code "as is", only inserted the range "B1:DA519". Any easy modification? Or is there a way to record the sorting for one row, and ad a code to the recorded macro to make it repeat it self for a number of times, always shifting to on row further down the worksheet? I created the following code: Sub Sort() Rows("93:93").Select Range("B93").Activate Selection.Sort Key1:=Range("A93"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Rows("94:94").Select Range("B94").Activate End Sub I hid column A, so it is not included in the sorting. I started the record tool, and started by highligthing a rom (no. 93), made the sort, and moved down to row 94. Could this be a way?? Regards Petter Q. "Tom Ogilvy" wrote: Sub RemoveBlanks() Range("B:AZ").SpecialCells(xlBlanks).Delete Shift:=xlShiftLeft End Sub would be another guess. Test it on a copy of your data. -- Regards, Tom Ogilvy "Petterq" wrote in message ... Hi. This is something else. On this link i find tips to fill inn blanks, but that is not the problem. I need to arrange all celles that have a value from column A and onwards through B, C and so on. As many columns needed to room all the values. The number of columns needed will vary from customer to customer. Example: CustomerID 8000 in A1 Ordered items: B1:43, D1:55, H1:63 After arranging: CustomerID 8000 in A1 Ordered items: B1:43, C1:55, D1:63 For on row only it can be done with a sort function, but then I have to sort one row at a time. I will have spreadsheets with hundreds of rows, and then it will be to time consuming to take it row by row. Any suggestions to automate the procedure? "Tom Ogilvy" wrote: Try looking here for an initial solution: http://www.contextures.com/xlDataEntry02.html Debra Dalgleish's site -- Regards, Tom Ogilvy "Petterq" wrote in message ... I have an Excel spreadsheet that is imported from a Online orderingsystem, thus the format is predefined. I need to arrange the data properly in order to import theese data to Access. I use Office 2003. Each line starts with a customerID number in column A. I the first row is listed the different items that has been ordered. One item in each coumn. If the customer in line 4 has ordered the item listed in column C, the cell C4 will have the itemID of that item. The result is a spreadsheet where each customer has severeal items ordered, but each for each item that is not ordered, the corresponding cell is left blank. I need to arrange the data so that each line starts with the customerID (as now), and all the items is listed from column B onwards removing all the blanks, and disregarding the item names listed in row 1. Row 1 will be deleted before the import. Suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need an Idea on Arranging Data for a Chart | Charts and Charting in Excel | |||
re-arranging the data | Excel Discussion (Misc queries) | |||
Help Arranging Data on Spreadsheet | Excel Discussion (Misc queries) | |||
Arranging Excel Data | Excel Discussion (Misc queries) | |||
Re-Arranging a Block of Data | Excel Programming |