How to remove special duplicate?

[MacFil]

Hi all. I'm new to Excel. I mostly work with Access. I made a search but could not find what I was looking for. I have a report in Access that I transfer to Excel. What I would like to acheive is to revove duplicate line but only some special duplicate.

Railcars 11-04-2005-3 .xls
	A	B	C	D	E	F	G	H	I	J
1	Railcar#	Code		Heat#	Std#	Grade	Qty	Sch	Size	Location/Remarks
2	102	QI602	N	W85463-2	QI226	81/84V0000	40	23	8.50
3	102	QI602	N	W85463-2	QI226	81/84V0000	40	31	8.50
4	102	QI602	N	W85464	QI226	81/84V0000	2	2	8.50
5
6	104	QI600	N	W86137	QI216	76/7900000	38	13	9.00
7	104	QI600	N	W86137	QI216	76/7900000	38	25	9.00
8
9	135471	QI602	N	W86948	QI131	1038CH	35	13	8.50
10	135471	QI602	N	W86948	QI131	1038CH	35	22	8.50
11	135471	QI602	N	W86947	QI131	1038CH	8	32	9.00
12	135471	QI602	N	W86947	QI131	1038CH	8	26	9.00
Railcars to unload Query

In this example, you have the Heat # coming up a couple time. I must say where the column Sch is where the heat is shecule under different PO.
So you have the same Heat # coming back in the same railcar.
I would like to keep only one heat out of 2 or 3 or 4..

How can it be done?

Thanks for your help.
JP

 

[tactps]

You culd run a macro on this. I have assumed that you are basing the deletions on the reference in column A

sub macro()
dim cell as range
range("A2:A"&[a65536].end(xlup).row)select
for each cell in selection
if cell = cell.offset(-1,0) then cell.offset(0,3).clearcontents
next cell
end sub

 

[MacFil]

tactps, thanks for your reply

I just tried the macro and at the line Range("A2:A"&[a65536].end(xlup).row)select
It gave a compile error at Range. Invalide use of Property.

What does it mean?

Thanks
JP

 

[Norie]

JP

Why can't you use a query in Access to do this?

 

[MacFil]

Got the Macro running

I gor the marco running. It was a syntax error on my part.
This macro does part of what I need. I need to delete the row instead of the cell.

Nori

I've been working on this database for the past 6 month. Before me, the other guy tried and could not come up with a solution. I'm just a beginner in Access and I did all my research to all my problems except this one. It look like it would be easy to delete row in Excel.

JP

 

[Norie]

JP

This could easily be done in Access using a simple query.

What would you want to happen to fields like quantity, size etc?

 

[MacFil]

Hi Norie

What I'm trying to get at is to eliminate duplicate row where the sch is more that once. The Qty will be the same. Every Sch represent a diferrent po.
The reference comes from column D for the heat#.
In Access, my query is a Union query to get my report, then I transfer the report to Excel to be re-format via macro and then I enter in the column remarks the location where to unload material.

Thanks for your help.
JP

 

[Norie]

Did you try any queries based on the union query?

What exact results would you want from the posted data?

By the way why are you using a union query?

Do you have multiple tables in Access that have the same data structure?

 

[MacFil]

The query is base on the union query. What I have is 10 link tables. Data comes from another system and I get data in txt file which I link. In the original files. we have a location for railcars, but when we have to combine multiple cars together because of the same heat # in defferent cars the we use the remark line.

Railcars 11-07-2005.xls
	A	B	C	D	E	F	G	H	I	J
1	Railcar#	Code		Heat#	Std#	Grade	Qty	Sch	Size	Location/Remarks
2	138364	QI602	N	W87008	QI554	1022MDCH	15	10	8.00
3	138364	QI602	N	W87008	QI554	1022MDCH	15	49	7.50
4	138364	QI602	N	W87009	QI554	1022MDCH	28
5
6	188120	QI602	N	W87008	QI554	1022MDCH	44	10	8.00
7	188120	QI602	N	W87008	QI554	1022MDCH	44	49	7.50
8
9
10	138364	QI602	N	W87008	QI554	1022MDCH	15	49	7.50
11	138364	QI602	N	W87009	QI554	1022MDCH	28
12
13	188120	QI602	N	W87008	QI554	1022MDCH	44	49	7.50
Railcars to unload Query

What I would like to achieve is to have the first 2 cars look like the last 2 cars.

Can you point me to the right direction?
I'm learning as I go. I have a lot to learn.

JP

 

[tactps]

If the macro does it, replace:
if cell = cell.offset(-1,0) then cell.offset(0,3).clearcontents
with
if cell = cell.offset(-1,0) then cell.entirerow.delete