Creating a customer overview tab from a table of data?

[AJGriffin100]

Hi All

I run a small family building company and currently use an Excel sheet to track different elements of the workflow of a customer job. eg. The few first columns have separate names for customer name, address, mobile and then the next few columns relate to workflow needed eg customer visited, estimate sent, the next few columns then relate to price of estimate, cost of labour, materials etc and then finally some columns on invoice sent, payment received, receipt sent.

The excel sheet is fairly large with a number of customers and several columns across the page that I have to scroll across. Is there is a way of making an overview tab where I can filter by a customer and have all their information going down the page in rows?

I have tried cutting and pasting into a new sheet using the "Transpose" paste special but I'd prefer it so that I save data for only one customer.

Any help really appreciated!

Thanks
Amanda

 

[etaf]

you have version 365 - so the transpose() & filter function should work

=TRANSPOSE(FILTER(data range,Customer name range="Customer name"))

Book1
	A	B	C	D	E	F	G	H	I
1	cust	1	2	3	4	5	6	7
2	name1	A1	A2	A3	A4	A5	A6	A7
3	name2	B1	B2	B3	B4	B5	B6	B7
4
5
6
7	Customer Name	Name1
8		A1
9		A2
10		A3
11		A4
12		A5
13		A6
14		A7
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
B8:B14	B8	=TRANSPOSE(FILTER(B2:H3,A2:A3=B7))
Dynamic array formulas.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you double click on a cell in column (A)
That row of data will be copied to sheet named "Overview"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  3/27/2021  12:45:50 PM  EDT
If Target.Column = 1 Then
Cancel = True
Dim Lastcolumn As Long
Lastcolumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Cells(Target.Row, 1).Resize(, Lastcolumn).Copy
Sheets("Overview").Cells(1, 1).PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Application.Goto Sheets("Overview").Range("A1")
End If
End Sub

 

[AJGriffin100]

you have version 365 - so the transpose() & filter function should work

=TRANSPOSE(FILTER(data range,Customer name range="Customer name"))

Book1
	A	B	C	D	E	F	G	H	I
1	cust	1	2	3	4	5	6	7
2	name1	A1	A2	A3	A4	A5	A6	A7
3	name2	B1	B2	B3	B4	B5	B6	B7
4
5
6
7	Customer Name	Name1
8		A1
9		A2
10		A3
11		A4
12		A5
13		A6
14		A7
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
B8:B14	B8	=TRANSPOSE(FILTER(B2:H3,A2:A3=B7))
Dynamic array formulas.

Thanks this is kind of along the lines of what I need but not sure I totally understand how to implement. This is the data set I am working from:

This is what I would like in a separate sheet (with just one customer):

 

[etaf]

your not going to get the blanks with the transpose filter formula
You would also need to add the titles into the new sheet, in the same order

IN A3 - you would need to copy A1 to ?1
And Transpose and paste special the titles into the sheet A3 - that will stay there fixed anyway
THEN
in column B3
PUT
=TRANSPOSE(FILTER(B2:?? ,A2:??="Customer name")). - where ?? is the end of the range , so column and row - Lets Assume is Z1000

=TRANSPOSE(FILTER(B2:Z1000 ,A2:A1000=B1))

Remove any sensitive data , and you could put on a share - dropbox or onedrive

 

[My Aswer Is This]

I see you like my solution but did not say if you want to use this ideal.
I can modify it to perform just like in your image you now show of how you want it.
But no need me modifying it if your not interested in this approach.

 

[AJGriffin100]

I see you like my solution but did not say if you want to use this ideal.
I can modify it to perform just like in your image you now show of how you want it.
But no need me modifying it if your not interested in this approach.

That would be fantastic if you could modify it for me. I couldn’t get the one above to work it just kept saying end / debug.

 

[My Aswer Is This]

That would be fantastic if you could modify it for me. I couldn’t get the one above to work it just kept saying end / debug.

Not sure what this means:
Your quote:
I couldn’t get the one above to work it just kept saying end / debug.
Do you mean the one I posted?

 

[My Aswer Is This]

What did my script not do that you wanted done?

 

[AJGriffin100]

What did my script not do that you wanted done?

Hi, so I right hand clicked on the tab, opened the code, and pasted the above and closed the window. When I then clicked on a name a pop-up came up saying debug / end but it didn’t create a copy of the data as expected.