![]() |
#1
|
|||
|
|||
![]()
Hi people,
I have one problem and would very appreciate your help. I've got a txt file with lots of data. In first column I have Country and for each country code of export product (but first on 4 digits as a category and than full codes for each product which is 8-10 digits), in second product details and in the third one export quantity. When I transfer the file into Excel, the table is following. Country/Code Description Values in EUR Afghanistan 3002 aaaaaaaaaaa 25.568.789 300220000 aaaaaaaaaaa 25.568.789 3005 aaaaaaaaaaa 20.145.698 300590211 aaaaaaaaaaa 5.000.000 300590311 aaaaaaaaaaa 15.145.698 8201 aaaaaaaaaaa 7.236.547 820110001 aaaaaaaaaaa 5.236.547 820110002 aaaaaaaaaaa 2.000.000 .... .... China 030371102 aaaaaaaaaaa 25.568.789 040110105 aaaaaaaaaaa 15.145.698 05040000 aaaaaaaaaaa 7.236.547 ... ... .. and smth about 100 000 rows. The question is how to divide this first column in two columns, so the table in the end looks like this: Country Code Description Values in EUR Afghanistan 300220000 aaaaaaaaaaa 25.568.789 Afghanistan 300590311 aaaaaaaaaaa 15.145.698 Afghanistan 300590211 aaaaaaaaaaa 5.000.000 Afghanistan 820110002 aaaaaaaaaaa 2.000.000 Afghanistan 820110001 aaaaaaaaaaa 5.236.547 China 030371102 aaaaaaaaaaa 25.568.789 China 040110105 aaaaaaaaaaa 15.145.698 China 050400007 aaaaaaaaaaa 7.236.547 Assuming VB should do it or??? Thanks a lot!!! Miroslav |
#2
|
||||
|
||||
![]()
Hello. This is one way of splitting the columns with text and numbers. It isn't the best or the only way but given your sample data, I have accomplished it. A macro enabled workbook is attached at the bottom of this post. The steps are included on the sheets, please start at Sheet Number 1. The code that is in the workbook is also included below if you need it.
Code:
Sub CleanUpVer1() ' ' CleanUpVer1 Macro ' IMPORTANT: Start in Cell A1 ' 'This macro will start in cell A1 ' and check each Row 's A and B cell's ' value. If Cell A and Cell B are the ' same, the macro will delete that row ' of data. If Cell A and Cell B are ' different, the macro will skip to the ' next row and continue to check each ' value until both Cell A and Cell B ' are blank. ' ' Keyboard Shortcut: Ctrl+w ' Dim ItemInCellA As Variant Dim ItemInCellB As Variant Do ItemInCellA = Selection ActiveCell.Offset(0, 1).Range("A1").Select ItemInCellB = Selection If ItemInCellA = ItemInCellB Then Selection.EntireRow.Delete ActiveCell.Offset(0, -1).Range("A1").Select Else ActiveCell.Offset(1, -1).Range("A1").Select End If Loop While (ItemInCellA <> "") And (ItemInCellB <> "") End Sub |
#3
|
|||
|
|||
![]()
Hi Boatwrenchv8,
thanks a lot for this!!!! It worked perfectly for the entire set of data. I owe you for this! Best, Miroslav |
#4
|
||||
|
||||
![]()
Miroslav,
You're welcome. Happy I could help. Please mark this thread as being solved. Regards, Rich |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA column header vs first row text | vthomeschoolmom | Excel Programming | 1 | 03-10-2012 02:05 AM |
Different column numbers, but same place on page -- what is going on?! Help | Straitsfan | Word | 3 | 03-01-2012 03:05 PM |
Two column format without affecting neither the page numbers nor the header or footer | Jamal NUMAN | Word | 4 | 02-21-2012 03:20 PM |
![]() |
dpeattie | Word | 6 | 12-29-2010 04:59 PM |
Can I auto increase numbers in a column? | compact | Word | 1 | 09-10-2009 07:16 AM |