Excel newbie request for help (sorting columns)

[Daisy]

I'm trying to sort a very large dataset which consists of several columns.

I need to sort the data set so that matching columns pair up... this is
easier to see in the following example:

http://uk.geocities.com/assortedtrash/excel.jpg

The SITC code columns need to match up, and I can't figure out any way of
doing that without going through by hand and inserting blocks of cells. Is
there some way to write a macro or otherwise sort this?

Any help would be appreciated.

Dx

 

[JulieD]

Hi Daisy

not sure if there's an easier way ... but this is might be one way of doing
it...

keep the existing data in sheet 1 of the workbook
in sheet 2 copy in the three EXPORT columns (A, B, C) and column D (the word
IMPORT)
then copy column B to column E
then in column F write
=IF(ISNA(VLOOKUP(Sheet2!B2,Sheet1!$B$1:$F$1000,5,0)),"",VLOOKUP(Sheet2!B2,Sh
eet1!$B$1:$F$1000,5,0))
copy this down column F
then the values where they match up will be populated in F and where they
don't it will be left blank,
you could then copy column F and paste special values to get rid of the
formula.
(this assumes that the code in column B only appears once in the data set)

you will then still need to compare the original column E with B to find the
values that were in E but not in B (have a look at
http://www.cpearson.com/excel/duplicat.htm for ways to approach this).

Hope this helps
Cheers
JulieD

 

[Daisy]

Hi Daisy

not sure if there's an easier way ... but this is might be one way of doing
it...

keep the existing data in sheet 1 of the workbook
in sheet 2 copy in the three EXPORT columns (A, B, C) and column D (the word
IMPORT)
then copy column B to column E
then in column F write
=IF(ISNA(VLOOKUP(Sheet2!B2,Sheet1!$B$1:$F$1000,5,0)),"",VLOOKUP(Sheet2!B2,Sh
eet1!$B$1:$F$1000,5,0))
copy this down column F
then the values where they match up will be populated in F and where they
don't it will be left blank,
you could then copy column F and paste special values to get rid of the
formula.
(this assumes that the code in column B only appears once in the data set)

you will then still need to compare the original column E with B to find the
values that were in E but not in B (have a look at
http://www.cpearson.com/excel/duplicat.htm for ways to approach this).

Hope this helps
Cheers
JulieD

Hi Julie,

Sorry for the delay in following up your reply. I just wanted to say thanks
for the info. Although it didn't do exactly what I wanted, it gave me 90% of
what I needed. I managed to get it to do what I wanted by ammending the
VLOOKUP command, a command I was previously unaware of until your post.

Thanks again,

D