Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
I want the formula to return either a BLANK if the cell is empty or the value of the cell. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
|
|||
|
|||
I've uploaded a copy of the workbook. Please take a look and let me know what you find out.
Thank you Again |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
|
|||
|
|||
THANK YOU!!!
That worked out perfectly. I greatly appreciate all of your efforts!! Now....do you know how to merge spreadsheets...? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
|
|||
|
|||
I've attached a sample here. Please give it a look and let me know.
Thank you again. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to merge two sheets | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
I have 100 excel sheets how can I merge all data into one sheet | Excel Worksheet Functions | |||
merge data from many sheets to one | Excel Worksheet Functions | |||
is there an easy way to merge data/information on several sheets . | Excel Discussion (Misc queries) |