0

I want to match a product number (column A) and a year (column B) for Country A with the same product and year for Country B to be able to compare the trade numbers (columns C and F) between the countries in that year and in that product. When no counterpart is found I want that post to be thrown out.

In the example in the link below I have manually done this until row 22. The row below for instance says that product 030420 was traded in 1995 at value 2.823, but no trade in that product i that year is recorded in country B, so that post I want deleted. Correspondingly, in Country B, trade of product 030420 is recorded in 1994, but there is no counterpart in country A, so that post also should be deleted. Is this doable? The data set is quite big and doing this manually could possibly take forever.

Example:

http://img228.imageshack.us/img228/1214/skrmavbild20110609kl092.png

Thanks Oscar

Oscar
  • 59
  • 3
  • so each instance in A and B of 030420 will be removed because all four do not share a common year? is that correct? – datatoo Jun 09 '11 at 12:08
  • you might find this useful http://stackoverflow.com/questions/1230864/excel-filter-table-rows-by-specified-column-value – Amro Jun 22 '11 at 14:49

1 Answers1

0

I would recomend you to do the following this steps:

  • Add a columna with the "concatenate" results of the columns A and B (for country A).
  • Add a columna with the "concatenate" results of the columns D and E (for country B).
  • Search for a exact "match" between the added column. (doing from A to B or B to A would yield diferent results, but the ERROR result show where isn´t a match and where you you look for data to erase).
  • A MultiLevel "sort" sould help to find the ERROR result and erase the bad data.
  • For a later phase add all his to a "macro".

I recommend to google any bolded word for doubts about how to do it.

Regards

Alen
  • 1,040
  • 1
  • 8
  • 13