move data with text identifiers

arlie

New Member
Joined
Aug 6, 2010
Messages
3
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>
<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>
<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>
<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>
<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>
<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>
<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

Fowmy

Well-known Member
Joined
Jul 10, 2008
Messages
512
Office Version
365
Platform
Windows
Try this formula:

=IFERROR(INDEX(Sheet3!$A$1:$F$4,MATCH($A2,Sheet3!$A$1:$A$23,0),MATCH(Sheet4!B$1,Sheet3!$A$1:$J$1,0)),0)

I put the orginal table this way
Excel Workbook
ABCDEF
1storefrontproduct1product2product4product5product6
2maysville2317212219
3kenton35120216
4tarten26024159
Sheet3
Excel 2010
 

arlie

New Member
Joined
Aug 6, 2010
Messages
3
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.
 

arlie

New Member
Joined
Aug 6, 2010
Messages
3
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

Threads
1,106,516
Messages
5,511,754
Members
408,864
Latest member
cmajewsk

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...
Top