excel - How to create Vlookup formula to replace old data with new one -

my company receives weekly updates different providers.they give same type of data in different order ( ex: 1 company give column a= name / column b= quantity / column c= cargo. when other give column a= cargo / column b= name / column c = quantity ) . think need create vlookup formula automatically find new data, , replace old 1 in right place(if data changed last week). understand formula have different every provider since have different format. me, ll write order of columns in our template, followed order of 1 of providers: our template

a:reference number b:imo c:vessel name d:commodity e:product detail f:quantity g:departure country h:departure port i:eta j:etb k:etd l:current status m:destination continent n:destination country o:destination port p:charterer q:receiver 

providers template:

b:vessel name c:eta d:etb e:etd f:departure country g:departure port h:destination continent i:destination country j:destination port k:commodity l:product detail m:quantity n:name of vessel o:imo p:reference code q:charterer r:current status. 

any idea how this???

okay based on last response, i'm going provide potential solution caveats , assumptions.

caveats

a vba solution may best here if data coming in multiple source workbooks. if happens infrequently wouldn't save time vba solution. if unfamiliar vba don't recommend paste in else's code got online - because malicious, or lead unable job if problem code arises can't fix.

assumptions

-i'm going assume each vendor included in same workbook, on own tab. -i'm assuming data going consistent, noted in comments question above. -for now, i'm assuming need few fields each vendor, , fields same between vendors. not required use method, simplifies things bit , start. now, let's assume care vendor name, cargo type, quantity, , expected shipping date.

proposed workflow

my general advice doing 'mini-database' in excel keep separate tabs data dumps, working formula sheets, , summary tabs used display/printing purposes. keep coding solutions level understand, , don't throw away raw sourcebooks. ideally if reviewing / auditing work should able copy & paste in random data sample , see if picking right amounts.

so, proposal have single summary tab, data-dump tab each vendor (also called "landing page"), , intermediary formula tab each vendor.

the data dump tabs should contain data comes in vendor, without adjustments. formatting data-dump tab match raw data (rather formatting raw data match data dump tab) mean each time update it, minimal intervention required. 1 step short of full 'automation'.

the intermediary formula tabs each supplier should in same format each other intermediary formula tab. allow easier pickup on main summary tab. purpose of intermediary formula tabs give opportunity @ vendor's excel datadump, , determine best way pull fields care review.

the summary tab list of vendor tabs, showing grid of fields care each.

steps complete

using "providers template" have in question, dump tab "provider data-dump" tab.

in "provider formulas" tab, first type provider's name sheet name. in cell a1, type "index". in cell b1, type "reference #". in cell c1, type "cargo type". in cell d1, type "quantity". in cell e1 type "shipping date".

column hold index equal number of orders being shipped vendor. formula in a2 "=1", , formula in a3, , copied down, be:

=if(row()<=count('provider data'!m:m),row()-1,"") 

this count row you're on, , if row you're on less total rows numbers in quantity field of raw data tab, show row number (-1 header). otherwise, show blank.

for rest of it, if data consistent, can set first cell in row 2 equal specific cell in other tab. ie: b2, shows reference id, simply:

='provider data'!p2 

then drag down , pick drag. use index key in column make sure have dragged far enough.

there formula solutions (like vlookup) used find specific reference id in raw data tab , pull exact row column, don't need use if keeping things in same row-order appear in data dump tab.

then summary tab, create columns pull in amounts each vendor. once have set workbook first time, you'll need update drag down rows in formula tab. if have further specific questions how achieve of this, please ask in specific question, noting have tried do, , part of has failed.

i hope helps started.