Advice how i would tackle this using an excel formula or vba solution

[VBA learner ITG]

Hi all,

I have over 500 spreadsheets which isnt formatted in the same way, I need to only capture the below column headers and associated data which can be in a row or column.

URN
Packaging
Delivery
Price

I need to capture the data in the below format to build a database:

Urn	DUN00266
Packaging	35.00
Delivery	TBC
Price	87.00

<tbody>
</tbody>

Example of how the data is formatted in various spreadsheets looks like the below:

Campaign

6PM CHRISTMAS OPENING TIMES STICKER

Campaign Version

Version1

PO Number

Start Date

09/11/2016

Created By

zack carter

Element

All

Store

All

Status

All

Printer

All

This Exported

03-11-2016 11:23

Element Code

Element Description

Element Type

URN

Height

Width

Material

Finishing

Cols Front

Cols Back

Colour Details

DSSame

DSDiff

Artwork Instructions

Keyword1

Keyword2

Keyword3

Keyword4

Keyword5

Print Process

NumStores

BriefDetails

Rule

Allocate Qty

Library Qty

% of Library

Total Qty

Price

DUN51136

Christmas- CHRISTMAS- 6PM

Sticker (36mm x 22mm)

DUN00266

36

22

Permanent Self-Adhesive

TTS

4

0

False

False

Cheapest

155

310

31

10.00 %

341

£52.00

Packing

£35.00

Delivery

TBC

TOTAL

£87.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 190.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

DUN00266

<tbody>
</tbody>

</body>

 

[ImranMalek]

Hi ,

Not sure if this can be done with a formula or not !

If you want to do it in VBA then you can think of finding a specific word from a sheet and then capture one cell right of it and below which you have to do for all the criterias and then you can filter the extracted tabular section on each sheet to remove unnecessary combinations.

quick code sample would be something like this ,

Sub extract()


    Dim rng As Range
    Dim cell As Range
    Dim extractword As String


    Set rng = Range("A1:AZ100")


    extractword = "Delivery"


    For Each cell In rng.Cells
        If Not cell.Find(extractword) Is Nothing Then
            Range("BB1").Value = cell.Offset(1, 0).Value
            Range("BB2").Value = cell.Offset(0, 1).Value
        End If
    Next cell


End Sub