Advice for summarising data

[LisaUK]

Hi everyone,

I am hoping someone can give me guide on a possible solution to what I am trying to. I currently have about 40 spreadsheets which all contain sales data from various areas in the world.
And for each area I am using the data to create a pivot table.
I have already managed to use some manual processes and from the help of some very helpful people on here got things done a little quicker.
I need a quicker process of even a software package if excel is not quick enough to consolidate the data and create reports from.
So to give you an idea the most time consuming one is normalising the data and cleaning it.

I have the following example for a region


Customer Item Sales Month
ABC 123 40000 Jan-11
ABC 444 20000 Jan-11
XXX 333 2000 Feb-11
DEF 333 43000 Jan-11



There are lots of other fields which I do not really require.
My end result is to get:


Month
Customer Jan Feb Mar Apr
Total Sales per Customer



The problem is these various spreadsheet contain sales data which has customers who are the same but spelt differently


For example a customer ABC will be ABC Corp on one file and then in another file it maybe ABC Corporation. I am trying to find a solution other than manually going and cleaning this up to have a quicker automated way of cleaning this data so I do not any repeats on the final pivot table. So ABC Corp And ABC Corporation are down as ABC.

I hope this makes sense, any help on this is appreciated

Thanks
Lisa

 

[venkat1926]

I am giving a hybrid solution
your data is like this. I have added one more row.

Excel Workbook
	A	B	C	D
1	Customer	Item	Sales	Month
2	ABC	123	40000	11-Jan
3	ABC corp	444	20000	11-Jan
4	XXX	333	2000	11-Feb
5	DEF	333	43000	11-Jan
6	DEF corporation	444	50000	12-Feb
Sheet1

now run this macro

Sub test()
Dim ra As Range, ca As Range, cust As Range, rfull As Range
Dim j As Integer, x As String
j = 1
With Worksheets("sheet1")
Set ra = Range(.Range("A2"), .Range("A2").End(xlDown))
Set cust = .Range("A1").End(xlDown).Offset(5, 0)
Set rfull = .Range("A1").CurrentRegion
For Each ca In ra
cust.Cells(j + 1, 1) = Left(ca, 3)
j = j + 1
Next ca
cust = "customer"
Range(cust, cust.End(xlDown)).AdvancedFilter xlFilterCopy, , cust.End(xlDown).Offset(5, 0), True
j = 1
Do
.Cells(Rows.Count, "A").End(xlUp).End(xlUp).Offset(0, j) = j
j = j + 1
If j = 13 Then Exit Do
Loop
End With
End Sub

goto the last table (in this case starting from row no. 21
the column A in this table and row 21 must have been already filled by the macro. You have to enter the formula in B22 which is to be copied down and right (formla easier to write in spreadsheet than in macro)
it will be like this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
21	customer	1	2	3	4	5	6	7	8	9	10	11	12
22	ABC	60000	0
23	XXX	0	2000
24	DEF	43000	50000
Sheet1