Excel Guru needed

kazbarina · Apr 1, 2011

I am trying to work out quite a complicated lookup and think I will need to use VBA but am stuck

I am trying to populate store numbers in a location grid:

Stores === No. of locations needed
50582 === 1
90789 === 1
90890 === 3
33333 === 1
44444 === 1
55555 === 2

The grid is numbered sequentially, with even numbers on one side and odd on the other:
Sequence ----- Location Number --------- Sequence ------- Location Number
1 ---------------- 11 -------------------- 2 ------------------ 12
3 ---------------- 13 -------------------- 4 ------------------ 14

What I need to do is taking the allocate the number of locations needed into the grid using the sequence, (i.e. 1,2,3,4,) but where more than one location is needed, these need to be alloccated only on one side of the grid (i.e. locations kept together), but then continuing on, backfilling the other side:

For example
Sequence -- Location Number -- Allocate --------- Sequence -- Location Number -- Allocate
1 ----------------11 ---------- 50582 ----------- 2 ------------ 12----------- 90789
3 ----------------13 ---------- 90890 ----------- 4 ------------ 14----------- 33333
5 ----------------15 ---------- 90890 ----------- 6 ------------ 16----------- 44444
7 ----------------17 ---------- 90890 ----------- 8 ------------ 18

Any help is appreciated

kazbarina · Apr 1, 2011

Further clarification:

I am trying to allocate the stores in the sequence listed into the locations on the cage grid, also in their sequence listed (i.e. 1,2,3,4,5)

Peter_SSs · Apr 1, 2011

See if this is what you want. Test in a copy of your workbook.

It assumes that before the code is run the sheet just contains the list of stores and the numbers needed (A1:B7) in my sheet below. My screen shot shows the sheet after the code has been run.

 Sub Locations() Dim Stores As Range, Store As Range Dim Num As Long, oSetCol As Long, oSetRow As Long Dim oddrws As Long, evenrws As Long, HdrRw As Long Dim bEven As Boolean Application.ScreenUpdating = False Set Stores = Range("A2", Range("A1").End(xlDown)) HdrRw = Stores.Rows.Count + 4 With Range("A" & HdrRw).Resize(, 3) .Value = Array("Sequence", "Loc Num", "Allocation") .Offset(, 3).Value = .Value End With bEven = False For Each Store In Stores With Store Num = .Offset(, 1).Value oSetRow = IIf(bEven, evenrws, oddrws) oSetCol = IIf(bEven, 3, 0) Cells(HdrRw + 1 + oSetRow, 3 + oSetCol).Resize(Num).Value = Store.Value If bEven Then evenrws = evenrws + Num Else oddrws = oddrws + Num End If bEven = evenrws < oddrws End With Next Store With Range("A" & HdrRw + 1).Resize(oddrws, 2) .Cells(1, 1).Resize(, 2).Value = Array(1, 11) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With With Range("D" & HdrRw + 1).Resize(evenrws, 2) .Cells(1, 1).Resize(, 2).Value = Array(2, 12) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With Application.ScreenUpdating = True End Sub

Excel Workbook

A

B

C

D

E

F

1

Stores

No.

2

50582

1

3

90789

1

4

90890

3

5

33333

1

6

44444

1

7

55555

2

8

9

10

Sequence

Loc Num

Allocation

Sequence

Loc Num

Allocation

11

1

11

50582

2

12

90789

12

3

13

90890

4

14

33333

13

5

15

90890

6

16

44444

14

7

17

90890

8

18

55555

15

10

20

55555

16

Locations

kazbarina · Apr 2, 2011

Peter,

That is absolutely fabulous.

Many, many thanks for you help

Karen

kazbarina · Apr 2, 2011

Hi Peter,

Just had a check now and there is just one further issue I need to recetify. When you get to sequence number 487 the location numbers change on the odd side and even side.

So where you have your stores list and No. There ideally would be two further columns - Column B would be location numbers by store to allocate up until 487 was reached. As soon as 487 was reached the allocation of the store numbers would be different for the odd and even side - so there would need to be two further columns i.e. 487 Odd and 488 even, and the allocation of the number of store locations would need to read from these two columns, depending if odd or even. The only major issue I can see is if the original allocation of lanes goes over 487 and 488 - as you'd be reading from two columns.

Stores No. No. 487 Odd No. 488 Even
50582 1 3 9
90789 1 3 9
90890 3 9 36
33333 1 3 9
44444 1 3 9
55555 2 6 12 If you allocattted this to 486 - it would say you needed to use 486 and 488
66666 1 3 9 But 488 onwards are tiny locations, hence you need more to allocate this store - 12 in effect.
77777 1 3 9 If the allocation trips over 487 or 488, your code should start the allocation of these stores at 487 or 488
88888 2 6 12
99999 1 3 9

Sequence Loc Num Allocation Sequence Loc Num Allocation
479 11 50582 480 12 90789
481 13 90890 482 14 33333
483 15 90890 484 16 44444
485 17 90890 486 18
487 488 20
489 490 20
491 492 20
493 494 20
495 496 20
497 498 20
499 500 20

Peter_SSs · Apr 2, 2011

I'm afraid I don't understand. Can you show a better screen shot of the data and the expected results to help demonstrate?

Consider one of the methods suggested in my signature block below, or at least set it out like your first post and give an indication of what the columns and rows are.

kazbarina · Apr 7, 2011

Sorry Peter

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

1

Store

Locations No. upto 487

No.Odd From 487

No. Even from 488

Sequence

Loc Num

Allocation

Sequence

Loc Num

Allocation

2

471

11

50592

472

12

92135

3

50592

1

9

5

473

13

12614

474

14

40711

4

92135

1

475

15

12438

476

16

40724

5

12614

1

2

477

17

40728

478

18

50824

6

40711

1

2

479

19

50830

480

20

12437

7

12438

1

2

481

21

12452

482

22

40720

8

40724

1

483

23

50838

484

24

40720

9

40728

1

2

3

485

25

12447

486

26

40720

10

50824

1

2

3

487

27

50076

488

28

40609

11

50830

1

2

489

29

50076

490

30

40609

12

12437

1

2

491

31

40611

492

32

40609

13

12452

1

2

493

33

52189

494

34

40615

14

40720

3

4

8

495

35

52189

496

36

40615

15

50838

1

2

497

35

52189

16

12447

1

17

50076

1

2

18

40609

1

2

3

19

40611

1

20

52189

2

3

5

21

40615

1

2

22

50055

1

23

50549

3

4

8

24

12416

1

25

12420

1

2

Sheet2

Peter_SSs · Apr 7, 2011

I *think* that I have understood, but not certain.

For that changed layout and new condition, try this.

 Sub Locations() Dim Stores As Range, Store As Range Dim Num As Long, oSetCol As Long, oSetRow As Long Dim oddrws As Long, evenrws As Long, HdrRw As Long Dim oSetNum As Long Dim bEven As Boolean, b487 As Boolean, b488 As Boolean Application.ScreenUpdating = False Set Stores = Range("A3", Range("A3").End(xlDown)) HdrRw = 1 With Range("F" & HdrRw).Resize(, 3) .Value = Array("Sequence", "Loc Num", "Allocation") .Offset(, 3).Value = .Value End With bEven = False oSetNum = 1 For Each Store In Stores With Store Num = .Offset(, oSetNum).Value oSetRow = IIf(bEven, evenrws, oddrws) oSetCol = IIf(bEven, 3, 0) Cells(HdrRw + 1 + oSetRow, 8 + oSetCol).Resize(Num).Value = Store.Value If bEven Then evenrws = evenrws + Num b488 = evenrws >= 243 Else oddrws = oddrws + Num b487 = oddrws >= 243 End If bEven = evenrws < oddrws If bEven Then oSetNum = 1 + IIf(b488, 2, 0) Else oSetNum = 1 + IIf(b487, 1, 0) End If End With Next Store With Range("F" & HdrRw + 1).Resize(oddrws, 2) .Cells(1, 1).Resize(, 2).Value = Array(1, 11) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With With Range("I" & HdrRw + 1).Resize(evenrws, 2) .Cells(1, 1).Resize(, 2).Value = Array(2, 12) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With Application.ScreenUpdating = True End Sub

kazbarina · Apr 11, 2011

Hi Peter

It works great thank you. Am sorry - all I keep giving you are more issues. The only outstanding issue now is where the allocation of stores into the locations trips into locations where the sequence is >=487. For example. Location 99999 from column 1 needs 3 locations and is allocated into locations with a sequence of 485, 486 and 487. As you know from previous, this would need to use column1 and column2; column1 for sequence less than 487 and column 2 for sequence> 487.

To get round this, I'd like to only allocate locations that fit exactly and doesn't trip across. I've attached an example to try to explain.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

1

Sequence

Loc Num

Allocation

Sequence

Loc Num

Allocation

2

Stores

No.

No 2

No 3

473

483

52217

474

484

92212

3

52217

4

2

3

475

485

52217

476

486

92212

4

92212

4

2

3

477

487

52217

478

488

92212

5

92233

4

2

3

479

489

52217

480

490

92212

6

92248

4

2

3

481

491

92233

482

492

92248

7

12411

2

1

3

483

493

92233

484

494

92248

8

50592

3

2

1

485

495

92233

486

496

92248

9

92135

1

2

3

487

497

92233

488

498

92248

10

12614

4

2

3

489

499

12411

490

500

50592

11

40724

4

2

3

491

501

12411

492

502

50592

12

493

503

92135

494

504

50592

13

495

505

92135

496

506

12614

14

497

507

40724

498

508

12614

15

499

509

40724

500

510

12614

16

17

473

483

52217

474

484

92212

18

475

485

52217

476

486

92212

19

477

487

52217

478

488

92212

20

479

489

52217

480

490

92212

21

481

491

12411

482

492

50592

22

483

493

12411

484

494

50592

23

485

495

92135

486

496

50592

24

487

497

92233

488

498

92248

25

489

499

92233

490

500

92248

26

491

501

12614

492

502

40724

27

493

503

12614

494

504

40724

28

495

505

496

506

40724

29

If allocated 92233 here, using locations from column 1,

30

would require 4 locations and this would push over

31

into 487. Hence need to start 92233 from 487, reading

32

from column 2. Can only backfill, if column 2

33

allocation will fit EXACTLY into the slot.

New

Peter_SSs · Apr 11, 2011

Getting a bit complicated for a forum like this but see how this goes.

Sub Locations() Dim Stores As Range, Store As Range Dim Num As Long, TopRws As Long, LR As Long Dim TargetRow As Long, TargetCol As Long Dim otr As Long, etr As Long, obr As Long, ebr As Long Const AllocCol As Long = 8 '<- 1st Allocation column Const HdrRw As Long = 1 '<- Allocation header row Const SeqChange As Long = 487 '<- Where seq change occurs Application.ScreenUpdating = False TopRws = Int(SeqChange / 2) Set Stores = Range("A3", Range("A3").End(xlDown)) With Cells(HdrRw, AllocCol - 2).Resize(, 3) .Value = Array("Sequence", "Loc Num", "Allocation") .Offset(, 3).Value = .Value End With For Each Store In Stores If Cells(HdrRw + TopRws, AllocCol).Value = "" Then otr = Cells(HdrRw + TopRws, AllocCol).End(xlUp).Row Else otr = HdrRw + TopRws End If If Cells(HdrRw + TopRws, AllocCol + 3).Value = "" Then etr = Cells(HdrRw + TopRws, AllocCol + 3).End(xlUp).Row Else etr = HdrRw + TopRws End If If Cells(HdrRw + TopRws + 1, AllocCol).Value = "" Then obr = HdrRw + TopRws Else obr = Cells(Rows.Count, AllocCol).End(xlUp).Row End If If Cells(HdrRw + TopRws + 1, AllocCol + 3).Value = "" Then ebr = HdrRw + TopRws Else ebr = Cells(Rows.Count, AllocCol + 3).End(xlUp).Row End If With Store Num = .Offset(, 1).Value If etr < otr And etr + Num <= HdrRw + TopRws Then TargetRow = etr + 1 TargetCol = AllocCol + 3 ElseIf otr + Num <= HdrRw + TopRws Then TargetRow = otr + 1 TargetCol = AllocCol ElseIf ebr < obr Then TargetRow = ebr + 1 TargetCol = AllocCol + 3 Num = .Offset(, 3).Value Else TargetRow = obr + 1 TargetCol = AllocCol Num = .Offset(, 2).Value End If Cells(TargetRow, TargetCol).Resize(Num).Value = Store.Value End With Next Store LR = Cells(Rows.Count, AllocCol).End(xlUp).Row With Cells(HdrRw + 1, AllocCol - 2).Resize(LR - HdrRw, 2) .Cells(1, 1).Resize(, 2).Value = Array(1, 11) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With LR = Cells(Rows.Count, AllocCol + 3).End(xlUp).Row With Cells(HdrRw + 1, AllocCol + 1).Resize(LR - HdrRw, 2) .Cells(1, 1).Resize(, 2).Value = Array(2, 12) .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=2 End With Application.ScreenUpdating = True End Sub 

Store and location info for where the change occurs:

Excel Workbook

A

B

C

D

391

a366

3

4

8

392

a367

1

393

a368

1

2

394

a369

1

9

5

395

a370

1

396

a371

1

2

397

a372

1

2

398

a373

5

8

2

399

a374

1

400

a375

1

2

3

401

a376

1

2

3

402

a377

1

2

403

a378

1

2

404

a379

2

1

2

405

a380

3

4

8

406

a381

1

2

407

a382

1

408

a383

1

2

409

a384

1

2

3

410

a385

1

Locations

Allocations made by the above code for this info:

Excel Workbook

F

G

H

I

J

K

L

236

469

479

a364

470

480

a363

237

471

481

a365

472

482

a366

238

473

483

a367

474

484

a366

239

475

485

a368

476

486

a366

240

477

487

a369

478

488

a370

241

479

489

a371

480

490

a372

242

481

491

a374

482

492

a375

243

483

493

a376

484

494

a377

244

485

495

a378

486

496

a381

245

487

497

a373

488

498

a379

246

489

499

a373

490

500

a379

247

491

501

a373

492

502

a380

248

493

503

a373

494

504

a380

249

495

505

a373

496

506

a380

250

497

507

a373

498

508

a380

251

499

509

a373

500

510

a380

252

501

511

a373

502

512

a380

253

503

513

a382

504

514

a380

254

505

515

a383

506

516

a380

255

507

517

a383

508

518

a384

256

509

519

a385

510

520

a384

257

511

521

a386

512

522

a384

258

513

523

a386

514

524

a387

Locations

Excel Guru needed

kazbarina

New Member

Excel Facts

kazbarina

New Member

Peter_SSs

MrExcel MVP, Moderator

kazbarina

New Member

kazbarina

New Member

Peter_SSs

MrExcel MVP, Moderator

kazbarina

New Member

Peter_SSs

MrExcel MVP, Moderator

kazbarina

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Excel Guru needed

New Member

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock