Importing data that needs zero data in missing rows

[excelCADfan]

If this is possible it will be a miracle! I am importing information from 20 stores by item. If a store doesn't carry an item it is not creating any data for that store but in turn it throws my whole template out of whack. The graph would be a repetitive 1-20. Can I create this pattern and import the information to match while creating blank cells when the data isn't available? I manually entered the left column and the items columns "B" on are imported.

excel store column test.xls

Store SKU Description Mfg.# UPC
1 1 5170016 GUN,BB BUCK 000000002105 39256221051
2 2 5170016 GUN,BB BUCK 000000002105 39256221051
3 3 5170016 GUN,BB BUCK 000000002105 39256221051
4 4 5170016 GUN,BB BUCK 000000002105 39256221051
5
6
7 7 5170016 GUN,BB BUCK 000000002105 39256221051
8 8 5170016 GUN,BB BUCK 000000002105 39256221051
9 9 5170016 GUN,BB BUCK 000000002105 39256221051
10 10 5170016 GUN,BB BUCK 000000002105 39256221051
11 11 5170016 GUN,BB BUCK 000000002105 39256221051
12 12 5170016 GUN,BB BUCK 000000002105 39256221051
14 14 5170016 GUN,BB BUCK 000000002105 39256221051
15
16 16 5170016 GUN,BB BUCK 000000002105 39256221051
17 17 5170016 GUN,BB BUCK 000000002105 39256221051
18 18 5170016 GUN,BB BUCK 000000002105 39256221051
19 19 5170016 GUN,BB BUCK 000000002105 39256221051
20 20 5170016 GUN,BB BUCK 000000002105 39256221051
21 21 5170016 GUN,BB BUCK 000000002105 39256221051
5170016

[JBeaucaire]

Are you looking for a formula you can enter into A2 and copy downward and get the 1-21 numbers in the same offsetting rows every time?

If so, put this formula in A2, then copy down as far as needed:

Please Login or Register  to view this content.

[excelCADfan]

One detail that would throw this off, we skipped 13 as a store number. Any way to by-pass it?

[JBeaucaire]

Nothing simple formula-wise comes to mind. I'll ponder it today during breaks. Sorry. Strange stuff you guys come up with...

[tigeravatar]

excelCADfan,

Using your provided file, In cell B2 and copied down:

Please Login or Register  to view this content.

Then custom format those cells to:0;;; so that the 0's aren't displayed (they will show blank instead).

[ragulduy]

Jerry, maybe you can skip 13 with something like this (I just used curve fitting to come up with the numbers):

Please Login or Register  to view this content.

Not sure if it's particularly elegant though