data control HELP PLEASE

[mani_singh]

hi everyone hope you can help me, i need to do the following (but i'll type it like an excel spreadsheet to make it clearer) single worksheet with input data list in two columns (NAME, FIXER NO)


NAME FIXER NO

kb123 2
kb234 1
kb123 3
kb123 1
kb145 1

multiple instances of name used but the number of the fixer involved is diff in each instance, ultimatly what i need is a single macro which can convert the input list into what is below - no duplicates and a split of who fixed what and how many times!


NAME 0 1 2 3
kb123 1 1 1
kb234 1
kb145 1


imagine the grid, ok i know its a tall order just hope you guys may have an idea on how to sort this out

THANKS IN ADVANCE !
Mani

 

[venkat1926]

see the sample sheet and formulas in F1 and activate this formula with
control shift enter. copy F1 to G1,H1 etc till you get error valaue(#NUM)
copy F1 down F2 and F3 but slightly modify $E$1 in two places to $E$2 for F2 and to $E$3 for F3. now copy F2 to G2 H2 etc similarly for F3

I changed KB145 value for checking purposes.

(this is Frank Kables formula modified slightly

Book2
	A	B	C	D	E	F	G	H	I
1	kb123	2			kb123	2	3	1	#NUM!
2	kb234	1			kb234	1	#NUM!	#NUM!	#NUM!
3	kb123	3			kb145	5	#NUM!	#NUM!	#NUM!
4	kb123	1
5	kb145	5
Sheet1

 

[mani_singh]

NAME FIXER NO

kb123 2
kb234 1
kb123 3
kb123 1
kb145 1

i dont think i explained that very well! sorry - ok lets try again
the above table is what we have comming in. with item name and fixer applied.

there are going to be repeats of items names but they ref to the same item, therefore what we need to do is filter the duplicate names to a smaller non-duplicate list but break down the number of fixes each fixer performed! as below. the 0 1 2 3 indicate which fixer is involved the ones represent the number of fixes performed on that item by that fixer.



NAME 0 1 2 3

kb123 1 1 1
kb234 1
kb145 1


hope that help making this issue clearer, any help is appriciated!

thanks everyone![/b]

 

[mani_singh]

if i write what i need in psudo code maybe it'll make more sense!

(input data on sheet 1)


if data = duplicate copy to sheet 2

(on sheet 2)

break down subset list into table of fixers (same as second table on previous post - i.e. one name but displayed info on how many times a fixer has fixed using this name)

copy now single line of name and breakdown of fixers fixing results over to sheet 3

 

[venkat1926]

type in E1,E2,E3 the three unique items namely

kb123
kb234
kb145




A! to B5 is your data (see my previous sheet)



now try this macro

Sub test()
Dim rng As Range, c As Range
Dim k As Integer, i As Integer
Dim rng1 As Range
Set rng = Range([a1], [a1].End(xlDown))
Set rng1 = Range([e1], [e1].End(xlDown))
For Each c In rng1
k = WorksheetFunction.CountIf(rng, c)
For i = 1 To k
c.Offset(0, i) = 1
Next i
Next c
MsgBox "macro over"
End Sub

 

[mani_singh]

Highlight items in colour to find a pattern copy them and paste in to another sheet
All items not colours copy then paste in to another sheet

(matched)Highlight items countif
(unique non matched) Non highlight items countif

Get totals from both list then paste