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>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fowmy

Well-known Member
Joined
Jul 10, 2008
Messages
512
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,816
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top