Creating a customer overview tab from a table of data?

AJGriffin100

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,684
Office Version
  1. 365
Platform
  1. MacOS
you have version 365 - so the transpose() & filter function should work

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

Book1
ABCDEFGHI
1cust1234567
2name1A1A2A3A4A5A6A7
3name2B1B2B3B4B5B6B7
4
5
6
7Customer NameName1
8A1
9A2
10A3
11A4
12A5
13A6
14A7
15
16
17
18
Sheet1
Cell Formulas
RangeFormula
B8:B14B8=TRANSPOSE(FILTER(B2:H3,A2:A3=B7))
Dynamic array formulas.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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"
VBA Code:
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

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
you have version 365 - so the transpose() & filter function should work

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

Book1
ABCDEFGHI
1cust1234567
2name1A1A2A3A4A5A6A7
3name2B1B2B3B4B5B6B7
4
5
6
7Customer NameName1
8A1
9A2
10A3
11A4
12A5
13A6
14A7
15
16
17
18
Sheet1
Cell Formulas
RangeFormula
B8:B14B8=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:

1616867098628.png


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

1616867156801.png
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,684
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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?
 

AJGriffin100

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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