Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2 ranges
with each other and then extract all the data that are in column D, but not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

in F1 put the formula
=if(countif(A:A,D1)=0,D1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2 ranges
with each other and then extract all the data that are in column D, but
not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Thanks Tom,
can I use this in VBA code as well? I need this to be part of a bigger
procedure that I'm currently writing.
Thanks ;)

KJ

"Tom Ogilvy" wrote in message
...
in F1 put the formula
=if(countif(A:A,D1)=0,D1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2 ranges
with each other and then extract all the data that are in column D, but
not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Hi again. I just read my original posting and can see myself that not
everything is quite clear in my explanation.
I'll try and put it in another way:
I have a whole load of data that I am manipulating with code and end up with
a sheet that looks like this:
A B C D E F
G H
1 35017 25000 09715 17000
2 35018 14100 35017 24990
3 35019 13000 35018 14120
4 35021 26200 35019 13000
5 35115 25150 35020 7520
6 35222 12600 35021 26200
7 35234 11550 35057 5200
...
The data in column A and D are transaction ID's. The data in B and E are the
values.
What I am trying to do is to cut the tansaction ID's in column D that are
not featured in column A and paste them in column G and H (G for the ID and
H for the value).
So in the above example tansaction ID 09715 with value 17000 would be pasted
to columns G and H and the rest of columns D and E would move up one row.

Hope this clears up my question a bit.
Again thanks for any help.

kj


"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2 ranges
with each other and then extract all the data that are in column D, but
not in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Range("F1").Formula = "=IF(COUNTIF(A:A,D1)=0,D1,"""")"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Knut Dahl" wrote in message
...
Thanks Tom,
can I use this in VBA code as well? I need this to be part of a bigger
procedure that I'm currently writing.
Thanks ;)

KJ

"Tom Ogilvy" wrote in message
...
in F1 put the formula
=if(countif(A:A,D1)=0,D1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2
ranges
with each other and then extract all the data that are in column D, but
not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Dim rng as Range, rw as Long
Dim rngA as Range, cell as Range
set rngA = Range(cells(1,"A"),Cells(rows.count,"A").End(xlup) )
set rng = Range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rw = 1
for each cell in rng
if application.Countif(rngA,cell.Value) = 0 then
cells(rw,"F").Value = cell.Value
rw = rw + 1
end if
Next

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Thanks Tom,
can I use this in VBA code as well? I need this to be part of a bigger
procedure that I'm currently writing.
Thanks ;)

KJ

"Tom Ogilvy" wrote in message
...
in F1 put the formula
=if(countif(A:A,D1)=0,D1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2
ranges
with each other and then extract all the data that are in column D, but
not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Dim lastRow as Long, rw as Long
Dim rngA as Range
set rngA = Range(cells(1,"A"),Cells(rows.count,"A").End(xlup) )
lastRow = cells(rows.count,"D").End(xlup).row

rw = LastRow
for i = Lastrow to 1 step -1
if application.Countif(rngA,cells(i,"D").Value) = 0 then
cells(rw,"G").Value = cells(i,"D").Value
cells(rw,"H").Value = cells(i,"E").Value
cells(i,"D").Resize(1,2).Delete Shift:=xlShiftUp
rw = rw - 1
end if
Next
if isempty(cells(1,"G")) then
range(cells(1,"G"),cells(rw,"H")).Delete Shift:=xlShiftUp
End if

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Hi again. I just read my original posting and can see myself that not
everything is quite clear in my explanation.
I'll try and put it in another way:
I have a whole load of data that I am manipulating with code and end up
with
a sheet that looks like this:
A B C D E F
G H
1 35017 25000 09715 17000
2 35018 14100 35017 24990
3 35019 13000 35018 14120
4 35021 26200 35019 13000
5 35115 25150 35020 7520
6 35222 12600 35021 26200
7 35234 11550 35057 5200
...
The data in column A and D are transaction ID's. The data in B and E are
the
values.
What I am trying to do is to cut the tansaction ID's in column D that are
not featured in column A and paste them in column G and H (G for the ID
and
H for the value).
So in the above example tansaction ID 09715 with value 17000 would be
pasted
to columns G and H and the rest of columns D and E would move up one row.

Hope this clears up my question a bit.
Again thanks for any help.

kj


"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2 ranges
with each other and then extract all the data that are in column D, but
not in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Wow, works brilliantly.
You are a star. Thanks a million.

Regards

kj

"Tom Ogilvy" wrote in message
...
Dim rng as Range, rw as Long
Dim rngA as Range, cell as Range
set rngA = Range(cells(1,"A"),Cells(rows.count,"A").End(xlup) )
set rng = Range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rw = 1
for each cell in rng
if application.Countif(rngA,cell.Value) = 0 then
cells(rw,"F").Value = cell.Value
rw = rw + 1
end if
Next

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Thanks Tom,
can I use this in VBA code as well? I need this to be part of a bigger
procedure that I'm currently writing.
Thanks ;)

KJ

"Tom Ogilvy" wrote in message
...
in F1 put the formula
=if(countif(A:A,D1)=0,D1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2
ranges
with each other and then extract all the data that are in column D,
but
not
in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ

Even better solution.
Thanks a million Tom.

kj


"Tom Ogilvy" wrote in message
...
Dim lastRow as Long, rw as Long
Dim rngA as Range
set rngA = Range(cells(1,"A"),Cells(rows.count,"A").End(xlup) )
lastRow = cells(rows.count,"D").End(xlup).row

rw = LastRow
for i = Lastrow to 1 step -1
if application.Countif(rngA,cells(i,"D").Value) = 0 then
cells(rw,"G").Value = cells(i,"D").Value
cells(rw,"H").Value = cells(i,"E").Value
cells(i,"D").Resize(1,2).Delete Shift:=xlShiftUp
rw = rw - 1
end if
Next
if isempty(cells(1,"G")) then
range(cells(1,"G"),cells(rw,"H")).Delete Shift:=xlShiftUp
End if

--
Regards,
Tom Ogilvy

"Knut Dahl" wrote in message
...
Hi again. I just read my original posting and can see myself that not
everything is quite clear in my explanation.
I'll try and put it in another way:
I have a whole load of data that I am manipulating with code and end up
with
a sheet that looks like this:
A B C D E
F
G H
1 35017 25000 09715 17000
2 35018 14100 35017 24990
3 35019 13000 35018 14120
4 35021 26200 35019 13000
5 35115 25150 35020 7520
6 35222 12600 35021 26200
7 35234 11550 35057 5200
...
The data in column A and D are transaction ID's. The data in B and E are
the
values.
What I am trying to do is to cut the tansaction ID's in column D that are
not featured in column A and paste them in column G and H (G for the ID
and
H for the value).
So in the above example tansaction ID 09715 with value 17000 would be
pasted
to columns G and H and the rest of columns D and E would move up one row.

Hope this clears up my question a bit.
Again thanks for any help.

kj


"Knut Dahl" wrote in message
...
Good morning everyone.
I am currently trying to compare 2 ranges with each other.
One range is in column A and the other in column D.
Column D contain more data than Column A. I want to compare the 2
ranges
with each other and then extract all the data that are in column D, but
not in column A and put them in a new column (F for example).
It might sound really easy, but I have tried various if...then and
do...while constructs, but it looks like I'm stuck logically.
Does anyone have a clever idea of how to solve this?
Any help is greatly apreciated.

Thanks guys

KJ