Search code examples
excelexcel-formulaconsolidation

Data consolidation using Columns

We sell our product via a number of different retailers. These shops send us reports of what they have sold and we need to import this into our new stock management system as a master file.

The stores do not and will not provide the data in a uniform way based on our desired template.

I therefore need a way of consolidating ranges of data that without having a unique ID for each line, with minimal manual formatting from the data importer to help save time.

http://s14.postimg.org/arslbnnxt/excelconsolidation.jpg

As you can see in the 2 examples above, nothing is in the same order and some of the supplied fields haven't been used. The column headers are static, only the data is actually pasted.

I've tried using the Excel Data consolidation tool but the SUM function just wants to add everything up or simply reports a single value, I've played with Vlookup but I then need to dedicate a number of rows to each store which results in lots of empty rows.

If you can point me in the right direction I'm more than happy to research the tool or function I need, web searches keep bringing me back to the consolidation tool.

Many thanks,

Solution

  • Since you say that column headers are static, you actually do have a unique ID.

    The task can be accomplished by using HLOOKUP with Column headers as lookup value. However, the formula will depend on how you organize or store input sheets from different stores.

    Here I have tested a setup where each store input is pasted in consecutive sheets starting from 'Sheet1'. i.e. Store1 in Sheet1, store2 in Sheet2 ... with row 1 containing header.

    Then in consolidated sheet using helper columns for identifying sheet and row number, the formulas look like this

    in A2 =IFERROR(HLOOKUP(A$1,INDIRECT("Sheet"&$H2&"!A:O"),$I2,0),"")

    in H3 =IFERROR(IF(INDIRECT("Sheet"&H2&"!A"&I2+1)="",H2+1,H2),"") (if no more records, increment sheet number)

    in I3 =IFERROR(IF((H3-H2)=0,I2+1,2),"") (if sheet number changed, reset row number to 2)

    Initial value for H2 = 1 , I2 = 2

    enter image description here

    Test file: Storefile