move data with text identifiers


New Member
Aug 6, 2010
Please help!!! How can I get data from an initial data sheet to move to its proper column in an in-between sheet, with text as the column identifier?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

My goal is to convert a data sheet that imports numbers daily, into a fixed version of this sheet. I need to create this in-between sheet so that office staff can use their daily reports or templates. These reports will have formulas that will add different columns of data for sales info and comparisons.<o:p></o:p>
This ‘initial data sheet’ does not always have all our product columns because when there are no items sold of a particular product, the zero data column won’t pull in. Therefore, the column index number for each product is always changing on this initial sheet. So, then i must identify the column heads by the text heading or the product identity. Right?
I am attempting to create an ‘in-between sheet’, a ‘fixed’ sheet with all our columns of product. The ‘initial data’ would be told to move the product counts into their proper columns on the ‘in-between sheet.’ And reports/templates would be created, reusable, with constant formulas and all. <o:p></o:p>
I was being trying to use Hlookup and Match to accomplish this, but am lost.<o:p></o:p>
There are 121 columns of products, all identified by text in the column heads, and 21 rows of 'storefront locations.' Example follows:<o:p></o:p>
1. Initial data sheet: ( No product3 sold this week, so no column of data)<o:p></o:p>
storefront, product1, product2, product4, product5, product6,<o:p></o:p>
maysville, 23, 17, 21, 22, 19,<o:p></o:p>
kenton, 35, 12, 0, 21, 6<o:p></o:p>
tarten, 26, 0, 24, 15, 9<o:p></o:p>
2. In-between sheet: (With a column for product3)<o:p></o:p>
storefront, product1, product2, product3, product4, product5, product6<o:p></o:p>
maysville, 23, 17, 0, 21, 22, 19,<o:p></o:p>
kenton, 35, 12, 0, 0, 21, 6<o:p></o:p>
tarten, 26, 0, 0, 24, 15, 9<o:p></o:p>
3. Report sheets have formulas referring to fixed columns from in-between to create sales comparisons, etc.<o:p></o:p>

How can i move this data, with text as the column identifier? <o:p></o:p>
Got the following string from examples online…<o:p></o:p>
HLOOKUP($A5, 'initial Data'!A2:IV2, MATCH("product1",A5:IV5, 0), FALSE)<o:p></o:p>
Didn’t give errors. Just zeros. <o:p></o:p>
Please can you offer me some clues or what can I read on this or can this be done? I am not sure what I have done is even on the right path. Or what other options are. Does this scenario make sense? Thanks very much, j<o:p></o:p>

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest


Well-known Member
Jul 10, 2008
Office Version
Try this formula:


I put the orginal table this way
Excel Workbook
Excel 2010


New Member
Aug 6, 2010
Thank you!
Will this work if the data changes daily? The $A$23, I mean.
Am working on your suggestion. Can't quite grasp it yet. Am up looking IfError, Index and Match to understand this.


New Member
Aug 6, 2010
OK. Wrapped my brain around it. All works smoothly thanks to your advice!
Thanks again for the help. j

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...