Filling in blanks

[vols77]

When a text file is downloaded, it groups items such as
Part Desc Dept Qty

The problem is that in the text file say part A is listed in 3 different depts then it looks like:

Part Desc Dept Qty
A El 1 1
2 4
9 6
Totals: 11
B C2 2 3
6 1
Totals: 4
What I am looking for is a way to have the part and desc repeat for the other 2 lines (or how many needed). This is then exported into access for reports but I need to have the part and desc on all the appropriate lines.

I have tried this as a step process but it only works if the part is repeated only once:
step 1:
=IF(RIGHT(F603,5)="tals:","",A603)
step 2:
=IF(M604="","",IF(M604=0,M603,M604))

 

[SkipVought]

Hi,
[tt]
=IF(isblank(A603),A602,A603)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[xlhelp]

Come again. Mud may be a little more clearer.

Where are those formulas located? Where do you get columns F and M and so on?


Member- AAAA Association Against Acronym Abusers

 

[vols77]

Column names would help

A = Part number
B = Desc
C = N/A
D = Dept
E = NA
F = Qty (which is also the "Parts Totals:") when at the end of a part


by using the formula from Skip I was able to come up with this:
=IF(RIGHT(F594,5)="tals:",A593,IF(RIGHT(F593,5)="tals:","",IF(ISBLANK(A592),A591,A592)))

The only thing is that the formula limits to filling in 3 blanks and I would like to have something that can grow in the event there are several or few blanks.

Just thinking, almost needs to say copy the part number down until the "Parts Totals:" line, which skip then start over at the next line

 

[SkipVought]

BTW, you cannot ENTER this in column A.

Starting at the first row of your data, you would enter in the next empty adjacent column, in your case, column G.

So if the first row of data were 2...
[tt]
G2: =if(isblank(a2),a1,a2)
[/tt]
and copy across to column H

Then copy G2:H2 down thru all your lines of data.

Skip,
[sub]
[red][/red]
[/sub]

 

[vols77]

The formula works great if there is only one line under the main line, however for those who have 3 or more, starting with the 3rd line the part number does not carry across. I.E. from the example in the first post, the line with dept 9 and qty 6 for part A, when using the formula does not have the part number.

 

[macropod]

Hi vols77,

Try this-
In H1:K1, replicate your column headings
. In H2 insert: =IF($D2="Totals:","",IF(A2="",H1,A2))
. copy H2 to I2
. In J2 insert: =D2
. In K2 insert: =F2
. copy H2:K2 down as far as needed.

Cheers

[MS MVP - Word]

 

[SkipVought]

Sorry...
[tt]
G2: =if(isblank(a2),G1,a2)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[KenWright]

Did you look at the FAQs at all?

How to fill in the blanks in a list of data.
faq68-4741

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[vols77]

I have to say I did but did not find that one. Thank you all again so very much!

Guess, I need to work on my searching skills.

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------