If Statement, possible If(And) Question

[Cordury]

Good Morning,
I have a file with over 6,900 rows that originated out of Fox Pro. The issue is that the unique identifier (Store #) is in a row above the data and the part numbers are in row(s) below the Store #. The amount of parts differs for each store.
Example

Row 2- Store 17
Row 3 and 4 contain the parts Store 17 used.
Row 5- Store 6001
Row 6,7, & 8 contain the parts Store 6001 used.

Basically I want to add the store number to a column that corresponds with the part numbers used. In inserted 2 columns (A&B) in A1 there is the text “Store #”
In B3 I have a simple if statement =IF(C2=$A$1,D2,"&quot
In C2 is “Store #” and in D2 is the actual Store #. Now this formula returned what I needed for the first part number for each store. But how can I modify it to return a store number for each part?

I know this is confusing.
Thanks,
Cord

 

[xlbo]

This formula assumes that you have all the data in col A like

store 16
part#1
part#2
store 17
part#3
part#4
part#5

etc etc

in A2 enter:
=IF(UPPER(LEFT(A2,5))="STORE","",IF(UPPER(LEFT(A1,5))="STORE",A1,B1))

and copy down

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Cordury]

Thanks Geoff. However the data is set up as follows:


Column A B
Row 3 Store # 17
Row 4 123456 (which is the part for store 17)
Row 5 78910 (Which is the part for store 17)
Row 6 Store # 6001
Row 7 111213 (which is the part for store 6001)
Row 8 141516 (which is the part for store 6001)

But the issue is some stores have only 1 part (1 column) and others may have 6 (6 columns).

Thank you for your reply regardless. I will fool around with your formula.

 

[xlbo]

just a little fiddle - this would go in C2 and then copy down:
=IF(UPPER(A2)="STORE #","",IF(UPPER(A1)="STORE #",B1,C1))

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Cordury]

Geoff,
I think =IF(C2=$A$1,D2,IF(C3=$A$1,"",B2)) should work as well. I should have given more info in my first post.

Thanks again for you recommendations!