Use a MACRO to Merge two sheets of data
 

I have a report that's due very soon and I need help.

I have to combine two sheets of data into one based on a few criteria and sorted by date and these criteria.

I've uploaded a file to assist anyone who can help me.

What I have to do is merge the information based on date and vehicle from two separate sheets into a third. Then sort by date and vehicle.

The finished product should show the date, vehicle, mileage, and amount of fuel purchased.

Any/all help will be greatly appreciated.

Thank you

Return BLANK is cell value sought is blank
 

Hi Frank,

Am Mon, 21 Oct 2013 13:29:51 +0100 schrieb frankjh19701:

=INDEX('Sheet1'!D$2:D$192, MATCH(A29,'Sheet1'!C$2:C$178,0))

the formula then is very long :-(
=IF(INDEX(Sheet1!D$2:D$192, MATCH(A29,Sheet1!C$2:C$178,0))=0,"",INDEX(Sheet1!D $2:D$192, MATCH(A29,Sheet1!C$2:C$178,0)))

But you could avoid the showing of 0 by using custom numberformat e.g.
#,##0.00;-#,##0.00;
Or you use Conditional Formatting: If cell value = 0 font color is white


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Return BLANK is cell value sought is blank
 

I have a INDEX and MATCH formula set to look for certain values in
one sheet and return the data from adjacent cells. But, if the cell
the formula refers to is blank, it returns a "0" instead of just
leaving the cell blank. Does anyone know how I can change my formula:

=INDEX('Sheet1'!D$2:D$192, MATCH(A29,'Sheet1'!C$2:C$178,0))

to return a "BLANK" ??

Any and all assistance will be greatly appreciated.

Thank you

Change your last arg from 0 to "", perhaps?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Thank you Claus for the reply. When I tried the formula, it worked on the blanks, but when it encountered a cell that had a value in it, it came back as N/A.

I want the formula to return either a BLANK if the cell is empty or the value of the cell.

Any suggestions?

Use a MACRO to Merge two sheets of data
 

Hi Frank,

Am Mon, 21 Oct 2013 19:08:54 +0100 schrieb frankjh19701:

Thank you Claus for the reply. When I tried the formula, it worked on
the blanks, but when it encountered a cell that had a value in it, it
came back as N/A.

if you get a #N/A the value from A29 is not available in Sheet1.
If you use xl2007 or later you can try IFERROR:
=IFERROR(IF(INDEX(Sheet1!D$2:$D$192,MATCH(A29,Shee t1!C$2:C$178,0))=0,"",INDEX(Sheet1!D$2:$D$192,MATC H(A29,Sheet1!C$2:C$178,0))),"")


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

O.K. I tried the new formula and it leaves a BLANK cell BLANK, but it also returns a BLANK cell when there should be a value in it.

How can I fix this?

Use a MACRO to Merge two sheets of data
 

Hi Frank,

Am Tue, 22 Oct 2013 14:10:39 +0100 schrieb frankjh19701:

O.K. I tried the new formula and it leaves a BLANK cell BLANK, but it
also returns a BLANK cell when there should be a value in it.

the formula is tested and works.
If your value of A29 is into column C of sheet1 but the cell stays
blank, then the values have different formats, e.g. one is number the
other is text or you have in A29 or column C non visible characters like
spaces.
Can you upload the file and post the link here?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I've uploaded a copy of the workbook. Please take a look and let me know what you find out.

Thank you Again

Use a MACRO to Merge two sheets of data
 

Hi Frank,

Am Tue, 22 Oct 2013 19:27:32 +0100 schrieb frankjh19701:

I've uploaded a copy of the workbook. Please take a look and let me know
what you find out.

look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "Tractors & Trailers Sample"
Rightclick and download it. For me it works.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

THANK YOU!!!
That worked out perfectly.

I greatly appreciate all of your efforts!!


Now....do you know how to merge spreadsheets...?

Use a MACRO to Merge two sheets of data
 

Hi Frank,

Am Wed, 23 Oct 2013 13:30:09 +0100 schrieb frankjh19701:

Now....do you know how to merge spreadsheets...?

if the layout of both sheets is the same, then copy the second sheet
without headers and paste it to the first empty row of sheet1.
For better information explain your layout and how the merged sheet
should look like.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I've attached a sample here. Please give it a look and let me know.

Thank you again.

Use a MACRO to Merge two sheets of data
 

Hi Frank,

Am Thu, 24 Oct 2013 17:38:04 +0100 schrieb frankjh19701:

I've attached a sample here. Please give it a look and let me know.

please look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Fuel Usage"
Rightclick and download it.
Some examples I have done. If that is what you want you can do the rest.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,
The Merged sheet should have the following headers:
Date, Tractor #, Driver Name, Gallons purchased, Fuel Location

The trick is to merge the two sheets of data and have it formatted in a way that merges the dates with each vehicle as if the fuel purchases are in date order for each vehicle.

I've uploaded a sample here.

Eventually, what I want to be able to do is get all of the fuel purchases assembled and then get an average M.P.G. per vehicle.

Please let me know what you think.

Thank you