Selectively order data from specific customer on a new sheet, effectively creating a mini invoice of a few columns

[milonius]

Hi, I have tried searching the web and here for this problem but none seem to encapsulate all aspects I am looking for.

I want to be able to select a range(customer name) and have it list out each VIN they have, the Date it was entered, and the value for that line. I have attached an excerpt from the main log.

I perform this action weekly. each log entry from the week prior gets highlighted green and then new logs are entered at the bottom. So the solution will need to be able to expand or I should manually be able to select which rows to to incorporate. Ideally the outcome of the solution would be on a separate page in the workbook.

If able to use a drop down in place of the customer name for the solution(A16) this would be best, or I can create unique versions for each customer but that is not as preferred.

I currently use a few formulas that are listed(date entry for when the cell in b* gets created) . Not shown here are vlookup formulas to auto fill-in price and code source based on code type.

My goal with this is to be able to quickly copy and paste this info into an existing invoice on a separate website so the customer can have a more detailed breakdown with their bill.

Not sure if any of this is possible. My skill level for excel is watching videos and duplicating the code with a moderate level of understanding but not fully enough to create a code without guidance.

Thanks for all the help, if more info is needed let me know.

Aaron



ps. this is a minor snippet of the log, it has lots of entries and copy pasting is not viable any longer.
(I have edited out portions of this document due to sensitive nature)

	A	B	C	D	E	F	G	H	I	J
1	Date & Time	Customer Name	Code Type	Code Source	User ID	Vin #	Owner Name	License Plate #	PIN	Customer Cost
2	1/0/00 12:00 AM	Randy	ford	Tech1		1FTRF18	randy	839		35
3	2/11/18 4:16 PM	John	ford	Tech1		1FAFP44	Jonathan	20		35
4	2/13/18 1:31 PM	Randy	ford	Tech1		1fafp55	Mary	rzn		35
5	2/15/18 1:47 PM	Randy	ford	Tech1		2ftrx1cb	josh	282m		35
6	2/17/18 5:35 PM	John	Chrysler	Tech2		1J4GLW	todd	028m		40
7	2/22/18 1:35 PM	Randy	ford	Tech1		1ftrw0k	Travis	17njs		35
8	2/22/18 2:40 PM	John	ford	Tech1		1fafpw1	wendy	09wif		35
9	2/23/18 10:47 AM	Alon	Mitsubishi	site1		4A3AK0	Daniel J.	99zx		25
10	2/24/18 4:15 PM	Edward	ford	Tech1		3FA6P2	Meg	22		35
11
12
13
14
15
16	Randy	140
17		Date	Vin	Cost
18		1/0/00 12:00 AM	1FTRF18	35
19		2/13/18 1:31 PM	1fafp55	35
20		2/15/18 1:47 PM	2ftrx1cb	35
21		2/22/18 1:35 PM	1ftrw0k	35
22
23	John	110
24		Date	Vin	Cost
25		2/11/18 4:16 PM	1FAFP44	35
26		2/17/18 5:35 PM	1J4GLW	40
27		2/22/18 2:40 PM	1fafpw1	35
28
29	ETC ETC
30
31

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula A2 =IF(B2 <>"", IF(A2 ="", NOW(), A2), "") A3 =IF(B3 <>"", IF(A3 ="", NOW(), A3), "") A4 =IF(B4 <>"", IF(A4 ="", NOW(), A4), "") A5 =IF(B5 <>"", IF(A5 ="", NOW(), A5), "") A6 =IF(B6 <>"", IF(A6 ="", NOW(), A6), "") A7 =IF(B7 <>"", IF(A7 ="", NOW(), A7), "") A8 =IF(B8 <>"", IF(A8 ="", NOW(), A8), "") A9 =IF(B9 <>"", IF(A9 ="", NOW(), A9), "") A10 =IF(B10 <>"", IF(A10 ="", NOW(), A10), "") B16 =SUM(D18:D21) B23 =SUM(D25:D27) <tbody> </tbody>

<tbody>
</tbody>

 

[Special-K99]

Based on your example data
with customer name selected in A12

in B18
=IFERROR(INDEX($A$2:$JA$10,SMALL(IF(($B$2:$B$10=$A$12),ROW($A$2:$A$10)),ROW(A1))-(ROW(A$2)-1),1),"")

in C18
=IFERROR(INDEX($A$2:$JA$10,SMALL(IF(($B$2:$B$10=$A$12),ROW($A$2:$A$10)),ROW(A1))-(ROW(A$2)-1),6),"")

in D18
=IFERROR(INDEX($A$2:$JA$10,SMALL(IF(($B$2:$B$10=$A$12),ROW($A$2:$A$10)),ROW(A1))-(ROW(A$2)-1),10),"")

Array formulas, use Ctrl-Shift-Enter
copy down for each row you have in your table (this may not be feasible - array formulas are slow)

or if you can use AGGREGATE (Excel 2010+ only)

in B18
IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),1),"")
in C18
IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),6),"")
in D18
IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),10),"")

non array formulas

 

[milonius]

This works very well. My concern is i don't think this works if the main data is on a separate sheet? Also it looks like I have to define the rows. is it possible to define the start of the selection and then just say bottom? one week i might be on rows 100-150, then the next 151-200 etc.

Mainly though, id like it to pull the data from a separate sheet and fill in these areas on another. (MainLog is where the data is , WeeklyTotals is where the output should be. The MainLog is layed out the same as the above example, the WeeklyTotals is currently blank.)

I could copy paste the current section into a different sheet and adjust each range, but hoping i can set up something a little more hands-off.

Thanks for the help the first suggestion is great if nothing else works.

 

[milonius]

Forgot to mention I used the first set of formulas, not the aggregate. but I think either would work if you suggest one over the other. im using excel 2016

 

[Special-K99]

Just insert thje sheet name in the INDEX part if its on another sheet.

Start row is highlighted in red below
This is the standard template for that formula I use.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(($A$1:$A$5<>""),ROW($A$1:$A$5)),ROW(A1))-(ROW(A$1)-1),1),"")

AGGREGATE solution would be better if you can use it.

 

[milonius]

I tried inserting the aggregate formula into the same spot but it didnt do anything. it acted as text. how to work around this?

 

[milonius]

i wish you could edit posts. I forgot to add the = before the formula, it works now. I will just go in and manually adjust the ranges each week for now.
Thanks for the help!

 

[milonius]

SO, I tried to get these formulas to work from a different sheet and with a different layout than the first example i provided.


here is what my fresh templates look like, could you provide some insight as to why the code isnt working with just switching the page name and values? I am not familiar with either of the formulas you provided so I wasn't able to rebuild them from scratch though I tried

	A	B	C	D	E	F	G	H	I	J
100	Date	Customer Name	Type	Source	User	Vin	Owner Name	Plate	Key Code	Price
101	7/29/18 9:41 PM	Albert Levy	Acura	Dealer		123456				25
102	7/30/18 12:08 PM	Yehuda David	Honda	Dealer		123456				15
103	7/30/18 12:08 PM	Randy Singleton	Buick	GM Tech		123456				50
104	7/30/18 12:09 PM	Timothy McCants	Suzuki	Vinlocks		123456				16
105	7/30/18 12:09 PM	Edward Rodriguez	Saturn	GM Tech		123456				50
106	7/30/18 12:09 PM	Albert Levy	Lincoln	Ford Tech		123456				35
107	7/30/18 12:09 PM	Albert Levy	Subaru	Subaru Tech		123456				20
108	7/30/18 12:10 PM	Randy Singleton	Ford	Ford Tech		123456				35
109	7/30/18 12:10 PM	Yehuda David	Kia	AutoKeyCodes		123456				15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

MainLog​

Worksheet Formulas Cell Formula A101 =IF(B101 <>"", IF(A101 ="", NOW(), A101), "") A102 =IF(B102 <>"", IF(A102 ="", NOW(), A102), "") A103 =IF(B103 <>"", IF(A103 ="", NOW(), A103), "") A104 =IF(B104 <>"", IF(A104 ="", NOW(), A104), "") A105 =IF(B105 <>"", IF(A105 ="", NOW(), A105), "") A106 =IF(B106 <>"", IF(A106 ="", NOW(), A106), "") A107 =IF(B107 <>"", IF(A107 ="", NOW(), A107), "") A108 =IF(B108 <>"", IF(A108 ="", NOW(), A108), "") A109 =IF(B109 <>"", IF(A109 ="", NOW(), A109), "") D101 =IFERROR(VLOOKUP(C102,ITEMS,3,G115),"") D102 =IFERROR(VLOOKUP(C102,ITEMS,3,G115),"") D103 =IFERROR(VLOOKUP(C103,ITEMS,3,G116),"") D104 =IFERROR(VLOOKUP(C104,ITEMS,3,G117),"") D105 =IFERROR(VLOOKUP(C105,ITEMS,3,G118),"") D106 =IFERROR(VLOOKUP(C106,ITEMS,3,G119),"") D107 =IFERROR(VLOOKUP(C107,ITEMS,3,G120),"") D108 =IFERROR(VLOOKUP(C108,ITEMS,3,G121),"") D109 =IFERROR(VLOOKUP(C109,ITEMS,3,G122),"") J101 =IFERROR(VLOOKUP(C101,ITEMS,2,FALSE),"") J102 =IFERROR(VLOOKUP(C102,ITEMS,2,FALSE),"") J103 =IFERROR(VLOOKUP(C103,ITEMS,2,FALSE),"") J104 =IFERROR(VLOOKUP(C104,ITEMS,2,FALSE),"") J105 =IFERROR(VLOOKUP(C105,ITEMS,2,FALSE),"") J106 =IFERROR(VLOOKUP(C106,ITEMS,2,FALSE),"") J107 =IFERROR(VLOOKUP(C107,ITEMS,2,FALSE),"") J108 =IFERROR(VLOOKUP(C108,ITEMS,2,FALSE),"") J109 =IFERROR(VLOOKUP(C109,ITEMS,2,FALSE),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

And this is what the output page looks like

	A	B	C	D
1	Brandon Stone
2
3	DATE	VIN	COST
4
5
6
7
8
9
10
11
12
13

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

WeeklyTotals​

My log sheet is titled MainLog and the output sheet is titled WeeklyTotals


I also made the name selector a scroll-able list

I hope this is more clear with the separate sheets shown?

 

[milonius]

I am still having trouble getting this to work from another sheet. here is a visual of of MainLog file page, as well as the other page called WeeklyTotals. I tried editing the values to get this to work but its not doing anything. I am trying the AGGREGATE function as you suggested. Thanks for any further assistance.

MainLog

	A	B	C	D	E	F	G	H	I	J
100	Date	Customer Name	Type	Source	User	Vin	Owner Name	Plate	Key Code	Price
101	7/29/18 9:41 PM	Albert Levy	Acura	Dealer		123456				25
102	7/30/18 12:08 PM	Yehuda David	Honda	Dealer		123456				15
103	7/30/18 12:08 PM	Randy Singleton	Buick	GM Tech		123456				50
104	7/30/18 12:09 PM	Timothy McCants	Suzuki	Vinlocks		123456				16
105	7/30/18 12:09 PM	Edward Rodriguez	Saturn	GM Tech		123456				50
106	7/30/18 12:09 PM	Albert Levy	Lincoln	Ford Tech		123456				35
107	7/30/18 12:09 PM	Albert Levy	Subaru	Subaru Tech		123456				20
108	7/30/18 12:10 PM	Randy Singleton	Ford	Ford Tech		123456				35
109	7/30/18 12:10 PM	Yehuda David	Kia	AutoKeyCodes		123456				15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

MainLog​

Worksheet Formulas Cell Formula A101 =IF(B101 <>"", IF(A101 ="", NOW(), A101), "") A102 =IF(B102 <>"", IF(A102 ="", NOW(), A102), "") A103 =IF(B103 <>"", IF(A103 ="", NOW(), A103), "") A104 =IF(B104 <>"", IF(A104 ="", NOW(), A104), "") A105 =IF(B105 <>"", IF(A105 ="", NOW(), A105), "") A106 =IF(B106 <>"", IF(A106 ="", NOW(), A106), "") A107 =IF(B107 <>"", IF(A107 ="", NOW(), A107), "") A108 =IF(B108 <>"", IF(A108 ="", NOW(), A108), "") A109 =IF(B109 <>"", IF(A109 ="", NOW(), A109), "") D101 =IFERROR(VLOOKUP(C102,ITEMS,3,G115),"") D102 =IFERROR(VLOOKUP(C102,ITEMS,3,G115),"") D103 =IFERROR(VLOOKUP(C103,ITEMS,3,G116),"") D104 =IFERROR(VLOOKUP(C104,ITEMS,3,G117),"") D105 =IFERROR(VLOOKUP(C105,ITEMS,3,G118),"") D106 =IFERROR(VLOOKUP(C106,ITEMS,3,G119),"") D107 =IFERROR(VLOOKUP(C107,ITEMS,3,G120),"") D108 =IFERROR(VLOOKUP(C108,ITEMS,3,G121),"") D109 =IFERROR(VLOOKUP(C109,ITEMS,3,G122),"") J101 =IFERROR(VLOOKUP(C101,ITEMS,2,FALSE),"") J102 =IFERROR(VLOOKUP(C102,ITEMS,2,FALSE),"") J103 =IFERROR(VLOOKUP(C103,ITEMS,2,FALSE),"") J104 =IFERROR(VLOOKUP(C104,ITEMS,2,FALSE),"") J105 =IFERROR(VLOOKUP(C105,ITEMS,2,FALSE),"") J106 =IFERROR(VLOOKUP(C106,ITEMS,2,FALSE),"") J107 =IFERROR(VLOOKUP(C107,ITEMS,2,FALSE),"") J108 =IFERROR(VLOOKUP(C108,ITEMS,2,FALSE),"") J109 =IFERROR(VLOOKUP(C109,ITEMS,2,FALSE),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Output for WeeklyTotals

	A	B	C
1	Brandon Stone
2
3	DATE	VIN	COST
4
5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

WeeklyTotals​

I have tried adjusting this set of formulas(with no luck)

for Date
=IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),1),"")
for VIN
=IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),6),"")
for Total
=IFERROR(INDEX(A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),10),"")

 

[Special-K99]

Try changing it to

for Date
=IFERROR(INDEX(MainLog!A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(MainLog!B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),1),"")
for VIN
=IFERROR(INDEX(MainLog!A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(MainLog!B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),6),"")
for Total
=IFERROR(INDEX(MainLog!A$2:J$10,AGGREGATE(15,6,ROW(A$2:A$10)/(MainLog!B$2:B$10=$A$12),ROWS(A$2:A2))-(2-1),10),"")

If this doesn't work I'd need to look at the file so images will be no good.

You cant attach files on this forum. Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

Remove any sensitive data, create a mockup example if necessary.