data control HELP PLEASE

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
ABCDEFGHI
1kb1232kb123231#NUM!
2kb2341kb2341#NUM!#NUM!#NUM!
3kb1233kb1455#NUM!#NUM!#NUM!
4kb1231
5kb1455
Sheet1
 

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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

Well-known Member
Joined
Jul 24, 2007
Messages
583
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
Code:
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

Well-known Member
Joined
Jul 24, 2007
Messages
583
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
 

Forum statistics

Threads
1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

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