Auto fill group data using info from another worksheet? - Formula Needed.

[BoldB]

Microsoft Excel 2010, Windows 7


Hi all,

Looking for some help with a formula to auto fill data from one worksheet to another by using a formula:

On our first worksheet we have sales data with codes

The sales data is per a row and in that row is a code such as (A32) (C32) depending on product spec sold.

We want another 8 columns of associated data to be put in the next columns on the sales sheet.

I understand the data has to be referred to somewhere so on worksheet two we have all the codes listed down a column (eg Column A) and then next to it we have the next 8 columns with data relating to that code.

So how can we ask excel to copy the relevant data from the reference worksheet (worksheet 2) into our sales data (worksheet 1)

I was hoping at this stage for a formula based solution.

I know about scripts and macros BUT have NO experience, and need a quick fix solution at this point.

Also because of discrepancies in data, I believe a macro would need extra if but maybe rules to function correctly, where as a formula I can drag or copy down will work best for now, as I can adjust to suit the discrepancies in data.


For example, This is worksheet 1:

[TABLE="class: grid, width: 736"]
<tbody>[TR]
[TD]RECORD[/TD]
[TD]CODE[/TD]
[TD]GRABBED 1[/TD]
[TD]GRABBED 2[/TD]
[TD]GRABBED 3[/TD]
[TD]GRABBED 4[/TD]
[TD]GRABBED 5[/TD]
[TD]GRABBED 6[/TD]
[TD]GRABBED 7[/TD]
[TD]GRABBED 8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]D32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]I32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]J32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]K32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]L32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]M32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]J32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]K32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And this is worksheet 2

[TABLE="class: grid, width: 576"]
<tbody>[TR]
[TD="width: 64"]CODES[/TD]
[TD="width: 64"]GRAB 1[/TD]
[TD="width: 64"]GRAB 2[/TD]
[TD="width: 64"]GRAB 3[/TD]
[TD="width: 64"]GRAB 4[/TD]
[TD="width: 64"]GRAB 5[/TD]
[TD="width: 64"]GRAB 6[/TD]
[TD="width: 64"]GRAB 7[/TD]
[TD="width: 64"]GRAB 8[/TD]
[/TR]
[TR]
[TD]C32[/TD]
[TD]24x16[/TD]
[TD]£4.63[/TD]
[TD]£7.45[/TD]
[TD][/TD]
[TD]65[/TD]
[TD]43[/TD]
[TD]3[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]D32[/TD]
[TD]30x20[/TD]
[TD]£6.49[/TD]
[TD]£12.55[/TD]
[TD][/TD]
[TD]78[/TD]
[TD]52[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]E32[/TD]
[TD]36x24[/TD]
[TD]£11.30[/TD]
[TD]£17.36[/TD]
[TD][/TD]
[TD]93[/TD]
[TD]62[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]F32[/TD]
[TD]48x32[/TD]
[TD]£16.04[/TD]
[TD]£22.10[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]83[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]G32[/TD]
[TD]54x36[/TD]
[TD]£22.11[/TD]
[TD]£28.17[/TD]
[TD][/TD]
[TD]140[/TD]
[TD]93[/TD]
[TD]5[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]H32[/TD]
[TD]3 x 16x8[/TD]
[TD]£6.91[/TD]
[TD]£9.74[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]23[/TD]
[TD]7[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]I32[/TD]
[TD]3 x 24x12[/TD]
[TD]£10.76[/TD]
[TD]£16.83[/TD]
[TD][/TD]
[TD]64[/TD]
[TD]32[/TD]
[TD]7[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]J32[/TD]
[TD]3 x 28x14[/TD]
[TD]£12.71[/TD]
[TD]£18.78[/TD]
[TD][/TD]
[TD]74[/TD]
[TD]38[/TD]
[TD]7[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]K32[/TD]
[TD]3 x 40x20[/TD]
[TD]£19.90[/TD]
[TD]£25.96[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]52[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]L32[/TD]
[TD]3 x 48x24[/TD]
[TD]£36.18[/TD]
[TD]£42.24[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]63[/TD]
[TD]15[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]M32[/TD]
[TD]32x18[/TD]
[TD]£8.39[/TD]
[TD]£11.22[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]22[/TD]
[TD]9[/TD]
[TD]1.5[/TD]
[/TR]
</tbody>[/TABLE]


On Worksheet 1, we want it to insert the cells next to the corresponding code in worksheet 2 into the next 8 columns (so CELL C2 on worksheet 1 would be auto filled with the data from CELL B2 as the code is "C32", CELL C5 in worksheet 1 would have the same date autofilled as the code is also "C32" etc..).

First post so I hope I've posted this correctly with enough information. Any help will be much appreciated.

Kind Regards
Steven

 

[VoG]

You should be able to use VLOOKUP for this - see Excel VLookup Function Examples

 

[jamtay317]

this is assuming that your spreadsheet codes starts in A1
=INDEX(Sheet2!$B$2:$I$12,MATCH($B2,Sheet2!$A$2:$A$12,0),MATCH(H$1,Sheet2!$B$1:$I$1,0))
you will also have to change the column headers so that they match

 

[jamtay317]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]RECORD[/TD]
[TD="class: xl64, width: 64"]CODE[/TD]
[TD="class: xl64, width: 64"]GRAB 1[/TD]
[TD="class: xl64, width: 64"]GRAB 2[/TD]
[TD="class: xl64, width: 64"]GRAB 3[/TD]
[TD="class: xl64, width: 64"]GRAB 4[/TD]
[TD="class: xl64, width: 64"]GRAB 5[/TD]
[TD="class: xl64, width: 64"]GRAB 6[/TD]
[TD="class: xl64, width: 64"]GRAB 7[/TD]
[TD="class: xl64, width: 64"]GRAB 8[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]1[/TD]
[TD="class: xl64, width: 64"]C32[/TD]
[TD="class: xl64, width: 64"]24x16[/TD]
[TD="class: xl64, width: 64"]£4.63[/TD]
[TD="class: xl64, width: 64"]£7.45[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]65[/TD]
[TD="class: xl64, width: 64, align: right"]43[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[TD="class: xl64, width: 64"]D32[/TD]
[TD="class: xl64, width: 64"]30x20[/TD]
[TD="class: xl64, width: 64"]£6.49[/TD]
[TD="class: xl64, width: 64"]£12.55[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]78[/TD]
[TD="class: xl64, width: 64, align: right"]52[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64"]E32[/TD]
[TD="class: xl64, width: 64"]36x24[/TD]
[TD="class: xl64, width: 64"]£11.30[/TD]
[TD="class: xl64, width: 64"]£17.36[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]93[/TD]
[TD="class: xl64, width: 64, align: right"]62[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]4[/TD]
[TD="class: xl64, width: 64"]F32[/TD]
[TD="class: xl64, width: 64"]48x32[/TD]
[TD="class: xl64, width: 64"]£16.04[/TD]
[TD="class: xl64, width: 64"]£22.10[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]125[/TD]
[TD="class: xl64, width: 64, align: right"]83[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64"]C32[/TD]
[TD="class: xl64, width: 64"]24x16[/TD]
[TD="class: xl64, width: 64"]£4.63[/TD]
[TD="class: xl64, width: 64"]£7.45[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]65[/TD]
[TD="class: xl64, width: 64, align: right"]43[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]6[/TD]
[TD="class: xl64, width: 64"]D32[/TD]
[TD="class: xl64, width: 64"]30x20[/TD]
[TD="class: xl64, width: 64"]£6.49[/TD]
[TD="class: xl64, width: 64"]£12.55[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]78[/TD]
[TD="class: xl64, width: 64, align: right"]52[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl64, width: 64"]I32[/TD]
[TD="class: xl64, width: 64"]3 x 24x12[/TD]
[TD="class: xl64, width: 64"]£10.76[/TD]
[TD="class: xl64, width: 64"]£16.83[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]64[/TD]
[TD="class: xl64, width: 64, align: right"]32[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl64, width: 64, align: right"]2.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]8[/TD]
[TD="class: xl64, width: 64"]J32[/TD]
[TD="class: xl64, width: 64"]3 x 28x14[/TD]
[TD="class: xl64, width: 64"]£12.71[/TD]
[TD="class: xl64, width: 64"]£18.78[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]74[/TD]
[TD="class: xl64, width: 64, align: right"]38[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl64, width: 64, align: right"]3.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]9[/TD]
[TD="class: xl64, width: 64"]K32[/TD]
[TD="class: xl64, width: 64"]3 x 40x20[/TD]
[TD="class: xl64, width: 64"]£19.90[/TD]
[TD="class: xl64, width: 64"]£25.96[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]105[/TD]
[TD="class: xl64, width: 64, align: right"]52[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]10[/TD]
[TD="class: xl64, width: 64"]L32[/TD]
[TD="class: xl64, width: 64"]3 x 48x24[/TD]
[TD="class: xl64, width: 64"]£36.18[/TD]
[TD="class: xl64, width: 64"]£42.24[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]125[/TD]
[TD="class: xl64, width: 64, align: right"]63[/TD]
[TD="class: xl64, width: 64, align: right"]15[/TD]
[TD="class: xl64, width: 64, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]11[/TD]
[TD="class: xl64, width: 64"]M32[/TD]
[TD="class: xl64, width: 64"]32x18[/TD]
[TD="class: xl64, width: 64"]£8.39[/TD]
[TD="class: xl64, width: 64"]£11.22[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]43[/TD]
[TD="class: xl64, width: 64, align: right"]22[/TD]
[TD="class: xl64, width: 64, align: right"]9[/TD]
[TD="class: xl64, width: 64, align: right"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]12[/TD]
[TD="class: xl64, width: 64"]F32[/TD]
[TD="class: xl64, width: 64"]48x32[/TD]
[TD="class: xl64, width: 64"]£16.04[/TD]
[TD="class: xl64, width: 64"]£22.10[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]125[/TD]
[TD="class: xl64, width: 64, align: right"]83[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]13[/TD]
[TD="class: xl64, width: 64"]J32[/TD]
[TD="class: xl64, width: 64"]3 x 28x14[/TD]
[TD="class: xl64, width: 64"]£12.71[/TD]
[TD="class: xl64, width: 64"]£18.78[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]74[/TD]
[TD="class: xl64, width: 64, align: right"]38[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl64, width: 64, align: right"]3.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]14[/TD]
[TD="class: xl64, width: 64"]K32[/TD]
[TD="class: xl64, width: 64"]3 x 40x20[/TD]
[TD="class: xl64, width: 64"]£19.90[/TD]
[TD="class: xl64, width: 64"]£25.96[/TD]
[TD="class: xl64, width: 64, align: right"]0[/TD]
[TD="class: xl64, width: 64, align: right"]105[/TD]
[TD="class: xl64, width: 64, align: right"]52[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[BoldB]

Hi jamtay317,

Thanks, that worked perfectly

Looks like you know your stuff so if you could help me with this formula, would be of great help:

Our sales codes are as follows depending on what we sell:

[TABLE="class: grid, width: 179"]
<tbody>[TR]
[TD]Z3053[P31](00A)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0643(R)-M32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BM4010[M32](06S)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there a formula that would give us the code at the start IE anything before the Square "[" or Rounded "(" bracket in a the cell next to it so we would end up with this:

[TABLE="class: grid, width: 179"]
<tbody>[TR]
[TD]Z3053[P31](00A)[/TD]
[TD]Z3053[/TD]
[/TR]
[TR]
[TD]0643(R)-M32[/TD]
[TD]0643[/TD]
[/TR]
[TR]
[TD]BM4010[M32](06S)[/TD]
[TD]BM4010[/TD]
[/TR]
</tbody>[/TABLE]

Kind Regards
Steven

 

[VoG]

Try

=IF(ISNUMBER(FIND("[",A1)),LEFT(A1,FIND("[",A1)-1),LEFT(A1,FIND("(",A1)-1))

 

[BoldB]

Hi VoG,

Works Perfect, Thank you very much

Kind Regards
Steven

 

[BoldB]

Hi,

Lastly (hopefully) can anybody help with this formula:

With the same data as last time:

[TABLE="class: grid, width: 179"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Z3053[P31](00A)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0643(R)-M32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BM4010[M32](06S)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G4144[AA32](00A)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

We require the code from the cells (P31, M32, AA32 etc..) to be placed in the cell next to it so we end up with this:

[TABLE="class: grid, width: 179"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Z3053[P31](00A)
[/TD]
[TD]P31[/TD]
[/TR]
[TR]
[TD]0643(R)-M32[/TD]
[TD]M32[/TD]
[/TR]
[TR]
[TD]BM4010[M32](06S)[/TD]
[TD]M32[/TD]
[/TR]
[TR]
[TD]G4144[AA32](00A)[/TD]
[TD]AA32[/TD]
[/TR]
</tbody>[/TABLE]

The code is only ever after the "-" or within the "[]".

Kind Regards
Steven

 

[VoG]

Try

=IF(ISNUMBER(FIND("[",A1)),MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1),RIGHT(A1,LEN(A1)-FIND("-",A1)))

 

[BoldB]

Cheers VoG, Perfect again