Comparing two ranges and extracting non duplicate data

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

try

Please Login or Register  to view this content.

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

then drag fill down the column.

--
Regards,
Tom Ogilvy

"Knut Dahl" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> in F1 put the formula
> =if(countif(A:A,D1)=0,D1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "Knut Dahl" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > in F1 put the formula
> > =if(countif(A:A,D1)=0,D1,"")
> >
> > then drag fill down the column.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Knut Dahl" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > in F1 put the formula
> > =if(countif(A:A,D1)=0,D1,"")
> >
> > then drag fill down the column.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Knut Dahl" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > in F1 put the formula
>> > =if(countif(A:A,D1)=0,D1,"")
>> >
>> > then drag fill down the column.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Knut Dahl" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>
>