I have searched many posts but cant seem to find anything similar, so I am
having to ask for assistance please. Im sure it will be a piece of cake for
someone, given the very in-depth issues Ive been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I cant modify as
its not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesnt complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However Im obviously
missing something as this doesnt work.

Ive also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesnt work either.

I am very grateful for any help.
Les

hi
you are downloading the access report. what you need to do is download the
access query that the report uses. you problem will go away.

regards
FSt1

"lesg46" wrote:

I have searched many posts but cant seem to find anything similar, so I am
having to ask for assistance please. Im sure it will be a piece of cake for
someone, given the very in-depth issues Ive been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I cant modify as
its not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesnt complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However Im obviously
missing something as this doesnt work.

Ive also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesnt work either.

I am very grateful for any help.
Les

Hi,
Sorry, I should have been a bit more clear. The report has already been
exported from an Access database, so I receive it as a txt file. I have no
way of changing what comes out of the database.

Many thanks for taking the time to reply though.

Best regards
Lesley


"FSt1" wrote:

hi
you are downloading the access report. what you need to do is download the
access query that the report uses. you problem will go away.

regards
FSt1

"lesg46" wrote:

I have searched many posts but cant seem to find anything similar, so I am
having to ask for assistance please. Im sure it will be a piece of cake for
someone, given the very in-depth issues Ive been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I cant modify as
its not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesnt complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However Im obviously
missing something as this doesnt work.

Ive also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesnt work either.

I am very grateful for any help.
Les

Since treating the 'dates' as values doesn't seem to be working, that
suggests that, although the items LOOK like dates, they've been imported from
Access as Strings (which have a -0- value).
Try something like...
=IF(LEN(TRIM(B3))=0,A2,TRIM(B3))
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"lesg46" wrote:

I have searched many posts but cant seem to find anything similar, so I am
having to ask for assistance please. Im sure it will be a piece of cake for
someone, given the very in-depth issues Ive been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I cant modify as
its not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesnt complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However Im obviously
missing something as this doesnt work.

Ive also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesnt work either.

I am very grateful for any help.
Les

Hi Gary,
Thanks for your help. It does indeed work, but actually only after it
didn't work initially and my discovery of why not! The blank cells weren't
actually blank - they contained a space. Which now explains why my version
didn't work either.
Thanks very much though, I've added my new knowledge to my ever increasing
list of useful Excel things.
Best regards
Lesley

"Gary Brown" wrote:

Since treating the 'dates' as values doesn't seem to be working, that
suggests that, although the items LOOK like dates, they've been imported from
Access as Strings (which have a -0- value).
Try something like...
=IF(LEN(TRIM(B3))=0,A2,TRIM(B3))
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"lesg46" wrote:

I have searched many posts but cant seem to find anything similar, so I am
having to ask for assistance please. Im sure it will be a piece of cake for
someone, given the very in-depth issues Ive been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I cant modify as
its not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesnt complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However Im obviously
missing something as this doesnt work.

Ive also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesnt work either.

I am very grateful for any help.
Les

The easiest way....

Select the range of dates in column B
Hit function key F5SpecialBlanksOK
This will select all the empty cells within the range
Type an = sign then hit the up arrow directional key then hold down the CTRL
key and hit ENTER.

If you want to use a formula in a helper column...

With dates in column B starting at B1...

Enter this formula in C1: =B1

Enter this formula in C2 and copy down as needed:

=IF(B2="",C1,B2)


--
Biff
Microsoft Excel MVP


"lesg46" wrote in message
...
I have searched many posts but can't seem to find anything similar, so I am
having to ask for assistance please. I'm sure it will be a piece of cake
for
someone, given the very in-depth issues I've been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I can't modify as
it's not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the
date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesn't complete correctly.

I had thought the easiest way to get round this would be to insert a
column
before the Date column and insert a formula. I have tried
=IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date,
otherwise
insert the date from the row before. (There will never be an instance
where
the date for the first item purchased is blank.) However I'm obviously
missing something as this doesn't work.

I've also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesn't work either.

I am very grateful for any help.
Les

Hi Biff,
It was as a result of your first suggestion that I discovered why Gary's
solution didn't work. I couldn't select the Blanks in the date column.
Because there weren't any! So once I'd corrected this, it all worked
beautifully.

Thanks very much.
Best Regards
Lesley


"T. Valko" wrote:

The easiest way....

Select the range of dates in column B
Hit function key F5SpecialBlanksOK
This will select all the empty cells within the range
Type an = sign then hit the up arrow directional key then hold down the CTRL
key and hit ENTER.

If you want to use a formula in a helper column...

With dates in column B starting at B1...

Enter this formula in C1: =B1

Enter this formula in C2 and copy down as needed:

=IF(B2="",C1,B2)


--
Biff
Microsoft Excel MVP


"lesg46" wrote in message
...
I have searched many posts but can't seem to find anything similar, so I am
having to ask for assistance please. I'm sure it will be a piece of cake
for
someone, given the very in-depth issues I've been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I can't modify as
it's not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the
date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesn't complete correctly.

I had thought the easiest way to get round this would be to insert a
column
before the Date column and insert a formula. I have tried
=IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date,
otherwise
insert the date from the row before. (There will never be an instance
where
the date for the first item purchased is blank.) However I'm obviously
missing something as this doesn't work.

I've also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesn't work either.

I am very grateful for any help.
Les

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"lesg46" wrote in message
...
Hi Biff,
It was as a result of your first suggestion that I discovered why Gary's
solution didn't work. I couldn't select the Blanks in the date column.
Because there weren't any! So once I'd corrected this, it all worked
beautifully.

Thanks very much.
Best Regards
Lesley


"T. Valko" wrote:

The easiest way....

Select the range of dates in column B
Hit function key F5SpecialBlanksOK
This will select all the empty cells within the range
Type an = sign then hit the up arrow directional key then hold down the
CTRL
key and hit ENTER.

If you want to use a formula in a helper column...

With dates in column B starting at B1...

Enter this formula in C1: =B1

Enter this formula in C2 and copy down as needed:

=IF(B2="",C1,B2)


--
Biff
Microsoft Excel MVP


"lesg46" wrote in message
...
I have searched many posts but can't seem to find anything similar, so I
am
having to ask for assistance please. I'm sure it will be a piece of
cake
for
someone, given the very in-depth issues I've been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I can't modify
as
it's not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the
date
is missing for the second item.

I need to create a pivot table based on all this data, however as I
have
blank date cells, obviously the table doesn't complete correctly.

I had thought the easiest way to get round this would be to insert a
column
before the Date column and insert a formula. I have tried
=IF(B30,B3,B2).
i.e. if the cell to the right has a date in it, insert that date,
otherwise
insert the date from the row before. (There will never be an instance
where
the date for the first item purchased is blank.) However I'm obviously
missing something as this doesn't work.

I've also tried =IF(B30,B3,IF(B3<=0,B2)). But that doesn't work
either.

I am very grateful for any help.
Les