Using 3 sheets to build finance report

[Xlr00kie]

Very stuck here.

Need to build a report based off of a template (contains specific headers):
[TABLE="width: 1294"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Bill code[/TD]
[TD]Product[/TD]
[TD]ID[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Item No.[/TD]
[TD]Date ordered[/TD]
[TD]date sold[/TD]
[TD]sales[/TD]
[TD]Qty[/TD]
[TD] Payment
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
The data is a huge log file that includes a LOT more info than i need, but need to be able to pull individual lineitems for every transaction that meets a specific criteria (ex - where Bill code = Acct 992, populate the fields corresponding to the headers for every transaction. I'd have to replicate it for a few different reports/bill codes, but that's the gist of it. Can't figure out how to get any lookup to work

 

[L. Howard]

Well, the request doesn't look that troublesome, but you are way short on where is the information you want to return for Bill code Acct 992.

You mention threes sheet in your query title. How would anyone know what to suggest if you don't give details of the sheets, what's on them, how the return data is formatted on the sheets, ie. what columns, rows etc.

If you can post a link to an example workbook (small amount of data, don't need it all) and explain in detailed but simple terms what you want to happen and where you want it to happen, then maybe someone will take a shot at it.

I'll for sure take a look.

Regards,
Howard

 

[Xlr00kie]

Sorry for the vagueness - rough day at the office.

so i start with sheet1, with a template that i can't really change (i've tried, always have to go back to original format):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 131"]
<tbody>[TR]
[TD="class: xl79, width: 131"]Bill code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 202"]
<tbody>[TR]
[TD="class: xl80, width: 202"]Product Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl79, width: 76"]Cust ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 261"]
<tbody>[TR]
[TD="class: xl80, width: 261"]Cust Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl80, width: 73"]Invoice No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl80, width: 78"]Order date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl80, width: 78"]P/U date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 238"]
<tbody>[TR]
[TD="class: xl80, width: 238"] Salesman [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl80, width: 45"]Qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl80, width: 143"] Payment [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



To populate the table/sheet illustrated, i have to sort from the raw data on sheet 2 which contains a lot more than i really need. however, i only want a particular bill code to populate - example any lineitems that have bill code N15, i need to import that info into the cells on the sheet above.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]Fulfillment store[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl77, width: 108"]Order Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Originating store[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Store number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Delivery location[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Selling location[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl77, width: 108"]PU date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]bill code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Product name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]SKU[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl77, width: 108"]Salesman[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Cost[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Cust No.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="width: 108"]Cust Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl77, width: 108"]Invoice No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]North - 28th[/TD]
[TD]4/28/14[/TD]
[TD]Central - Main st[/TD]
[TD]2[/TD]
[TD]28[/TD]
[TD]28[/TD]
[TD]5/1/14[/TD]
[TD]N15[/TD]
[TD]18" Saw[/TD]
[TD]345221[/TD]
[TD]Steve Jackson[/TD]
[TD]$18.97[/TD]
[TD]9652[/TD]
[TD]John Blevins[/TD]
[TD]85412-1[/TD]
[/TR]
[TR]
[TD]South - Crawford[/TD]
[TD]4/14/14[/TD]
[TD]South - Crawford[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]4/20/14[/TD]
[TD]N10[/TD]
[TD]2 ton Floor Jack[/TD]
[TD]524100[/TD]
[TD]George Hopkins[/TD]
[TD]$34.85[/TD]
[TD]841115[/TD]
[TD]Mack Holiday[/TD]
[TD]941632-2[/TD]
[/TR]
</tbody>[/TABLE]



The raw data (above) is dropped on a network share from corp. IT - so i would have to manipulate it many times to put summaries where they need to go (by using the VLOOKUP or HLOOKUP). I totally appreciate any coaching!

 

[L. Howard]

So if you enter N15 in the Bill Code column on sheet 1 you want to populate the sheet 1 Header columns with the data from sheet 2 with the same Header?

That is to say, you will use the Bill Code on sheet 1 to call data from sheet 2.

Howard

 

[Xlr00kie]

pretty much, and to populate the headings with the corresponding data from those lineitems containing bill code N15 - so for every instance of N15 on the raw sheet, a new line gets created on sheet 1 with the info extracted from raw.

 

[L. Howard]

You have no Qty or Payment column on sheet2.

What do you want to do with them on sheet 1?

Howard

 

[Xlr00kie]

I forgot to add the quantity column in the example above, but the cost would import to the Payment.

 

[L. Howard]

Try this, where you have an example of formulas for a solution and VBA code for another.

EDIT: Bad copy will repost a link to a good copy

Formulas are in the upper small example and code in the bottom small sample.

In both examples, you will enter a ID number (N10 or N15 in this example) in column A.

Results are returned in the shaded field.

With the formula example note that column C & D are array formulas. To make changes to the formula for either column you must select both formulas in C & D together, and while selected, make any changes you need and with both cells still selected use ctrl + shift + enter. Excel will put { }'s around the formula, don't do it yourself.

Also in the formula example there are two VLOOKUP formulas that "look to the left" for the value to return. These are in columns F & G. They use CHOOSE so are easy to spot.

For me, I prefer the coded example over the formulas.

If you need help applying either to your real life workbook post back and we will see what we can do.

Regards,
Howard

 

[L. Howard]

Lets try this link.

https://www.dropbox.com/s/ru2k2y7f1ghv02j/XLrookie VLOOKUP and CODE example Drop Box.xlsm

Howard