compare and insert

  • Thread starter Thread starter R Krishna
  • Start date Start date
R

R Krishna

Hi

I am trying to get a macro to compare a list of names in ColA of one sheet
with blocks of names in ColB in another sheet to see if any names have been
left out, if so they need to be inserted in ColB. In the first sheet is a
list of products. in the second sheet is a list of shops and the items they
sell and the volume sold in the last 4 years

not all shops sell all items, to the second sheet in ColB does not list an
item the shop does not sell. Now I have to redo the list to show all the
items in product list in the ColB even if it is not sold by the shop.

Lots of shops, lots of item....
lots of help needed...

Thanks in advance

RKrishna

Sheet1 ColA
Apples
oranges
bananas
peaches
strawberry
coconuts
plums
dates
grapes

Sheet2
ColA
Shop1 Itemname itemcode 2002 2001 2000 1999
aaa, chennai Apples 01 150 250
158 160
aaa, Chennai oranges 02 250 225 225
214
aaa, Chennai bananas 03 250 225 225
214
aaa, Chennai peaches 04 250 225 225
214
aaa, Chennai strawberry 05 250 225 225
214

bbb, B'lore oranges 02 250 225
225 214
bbb, B'lore peaches 04 250 225
225 214
bbb, B'lore plums 07 355 266
155 622
bbb, B'lore grapes 09 255 622
155 355
 
Here's one way.

Paste the names from ColA of the first sheet into ColB of the 2nd, below the existing list.

Then on that 2nd sheet, select only ColB containing the shop names. Then go to
Data/Filter/Advanced. Select Filter-in-place, leave the Criteria range blank, and check the
Unique Records Only box. Click OK.

Then copy the filtered list -- but all 3 columns -- to another area.

Note: You can't tell Advanced Filter to copy the data to another location. That will copy only
items from column B, without the information in the other columns.
 
Hi
thank you, but that does not help, because there are many shops in the
second sheet and between them they cover all the items. so the filter does
not get very far.

Thanks for the tip

Rkrishna

Myrna Larson said:
Here's one way.

Paste the names from ColA of the first sheet into ColB of the 2nd, below the existing list.

Then on that 2nd sheet, select only ColB containing the shop names. Then go to
Data/Filter/Advanced. Select Filter-in-place, leave the Criteria range blank, and check the
Unique Records Only box. Click OK.

Then copy the filtered list -- but all 3 columns -- to another area.

Note: You can't tell Advanced Filter to copy the data to another location. That will copy only
items from column B, without the information in the other columns.
Click to expand...
 
Hi

The company sell different brand of stuff, but not all shops stock all
stuff.
when the data is entered,if the shop has not stocked any of the 120 brands
in the last 4 years then the brand is not listed at all, Column wise it
never exceeds 7 Columns. row are endless!

what I am trying to do is find in each shop what has not been sold then
insert rows to add the item, item no, then 0's in each Column for sale. that
way each shop will list all items whether sold or not.
Does sheet1 contains all the items and sheet2 is to
show items from sheet1 for each shop even if they do not sell that
item. You will have to sort sheet 2 on shop
and then item. You list of all items must be sorted whether it
is Sheet1 or the new sheet (on col b). The macro would have to
read down your sheet2 and for each shop insert any item they
do not currently sell.
Click to expand...


every row must begin with Shop name in ColA, then item name, is copied in,
item type is copied in and for data each col gets a 0. the Sort is not
needed, as it always in order, I just add at the end of each shop, so long
as the shop name is there in Col. this is then saved as "txt" and send back
to the database,

there are 40 workbooks, 1 for every District and about 80 to 120 shops a
district. I think they are trying some kind of analysis on what sells where,
how much and why.... the problem is the old system just keeps track of what
is sold, if it is not there it means it was not sold, this new format needs
to show 0. so all this work.

I did try a search and drew a blank, there were some close but not there yet
kind of compare & "do something" stuff... but all called for too much
tinkering.

Thanks for all the help

RKrishna
 
hi
i am not sure how much this will help but I am posting some data as example.

Assunme this is what I have in Sheet1
Constituency = shop Name
PARTY NAME= Item name
PARTY TYPE = Item Code
1999 to 1991 are the 4 years


Election Commission Of India - General Elections 1999
General Elections 1999 - KARNATAKA


Constituency PARTY NAME PARTY TYPE 1999 1998 1996 1991
Bidar(SC) BJP N 48 53.24 48.5 50.61
Bidar(SC) BSP N 6.83 5.24 4.01 NC
Bidar(SC) CPM N NC NC 14.67 NC
Bidar(SC) INC N 27.16 16.96 15.55 24.79
Bidar(SC) JD N NC 22.28 NC 23.04
Bidar(SC) JD(S) N 15.41 NC NC NC
Bidar(SC) JP S NC 1.17 1.74 NC
Bidar(SC) NCP S 2.61 NC NC NC
Bidar(SC) SAP S NC 0.48 NC NC
NC - Not Contested N - National Parties S -
State Parties




Constituency PARTY NAME PARTY TYPE 1999 1998 1996 1991
Gulbarga BJP N 38.15 44.71 33.15 28.98
Gulbarga BSP N 4.8 7.55 NC NC
Gulbarga INC N 47.6 19.15 23.35 43.94
Gulbarga JD N NC 26.8 35.89 NC
Gulbarga JD(S) N 8.89 NC NC NC
Gulbarga JP S NC NC NC 21.63
Gulbarga MUL S NC 0.12 NC NC
Gulbarga RJD S NC 0.19 NC NC
Gulbarga SAP S NC 1.04 NC NC
NC - Not Contested N - National Parties S -
State Parties


In the sheet2 thereis a full list of Party Names
this is how it looks

Bahujan Samaj Party BSP N
Bharatiya Janata Party BJP N
Communist Party of India CPI N
Communist Party of India (Marxist) CPM N
Indian National Congress INC N
Nationalist Congress Party NCP N
All India Anna Dravida Munnetra Kazhagam ADMK S
All India Forward Bloc FBL S
Dravida Munnetra Kazhagam DMK S
Janata Dal (Secular) JD(S) S
Janata Dal (United) JD(U) S
Rashtriya Janata Dal RJD S
Revolutionary Socialist Party RSP S
Samajwadi Party SP S
Samata Party SAP S
Shivsena SHS S
Tamil Maanila Congress (Moopanar) TMC(M) S
Telugu Desam TDP S

the result i am looking for is this

Election Commission Of India - General Elections 1999
General Elections 1999 - KARNATAKA


Constituency PARTY NAME PARTY TYPE 1999 1998 1996 1991
Bidar(SC) BJP N 48 53.24 48.5 50.61
Bidar(SC) BSP N 6.83 5.24 4.01 NC
Bidar(SC) CPM N NC NC 14.67 NC
Bidar(SC) INC N 27.16 16.96 15.55 24.79
Bidar(SC) JD N NC 22.28 NC 23.04
Bidar(SC) JD(S) N 15.41 NC NC NC
Bidar(SC) JP S NC 1.17 1.74 NC
Bidar(SC) NCP S 2.61 NC NC NC
Bidar(SC) SAP S NC 0.48 NC NC
Bidar(SC) SAP S NC NC NC NC
Bidar(SC) CPI N NC NC NC NC
Bidar(SC) NCP N NC NC NC NC
Bidar(SC) ADMK S NC NC NC NC
Bidar(SC) FBL S NC NC NC NC
Bidar(SC) DMK S NC NC NC NC
Bidar(SC) JD(S) S NC NC NC NC
Bidar(SC) JD(U) S NC NC NC NC
Bidar(SC) RJD S NC NC NC NC
Bidar(SC) RSP S NC NC NC NC
Bidar(SC) SP S NC NC NC NC
Bidar(SC) SHS S NC NC NC NC
Bidar(SC) TMC(M) S NC NC NC NC
Bidar(SC) TDP S NC NC NC NC
NC - Not Contested N - National Parties S -
State Parties



Constituency PARTY NAME PARTY TYPE 1999 1998 1996 1991
Gulbarga BJP N 38.15 44.71 33.15 28.98
Gulbarga BSP N 4.8 7.55 NC NC
Gulbarga INC N 47.6 19.15 23.35 43.94
Gulbarga JD N NC 26.8 35.89 NC
Gulbarga JD(S) N 8.89 NC NC NC
Gulbarga JP S NC NC NC 21.63
Gulbarga MUL S NC 0.12 NC NC
Gulbarga RJD S NC 0.19 NC NC
Gulbarga SAP S NC 1.04 NC NC
Gulbarga CPI N NC NC NC NC
Gulbarga CPM N NC NC NC NC
Gulbarga NCP N NC NC NC NC
Gulbarga ADMK S NC NC NC NC
Gulbarga FBL S NC NC NC NC
Gulbarga DMK S NC NC NC NC
Gulbarga JD(U) S NC NC NC NC
Gulbarga RSP S NC NC NC NC
Gulbarga SP S NC NC NC NC
Gulbarga SHS S NC NC NC NC
Gulbarga SHS S NC NC NC NC
Gulbarga TMC(M) S NC NC NC NC
Gulbarga TMC(M) S NC NC NC NC
Gulbarga TDP S NC NC NC NC
NC - Not Contested N - National Parties S -
State Parties

I hope this sets right the confusion I have created...

please help, because I have 40 workbooks full of this !!

ps. I am going out to the field, will be back in office only on 26th. I will
be able to test all your valuable inputs only then.

regards

TIA

R Krishna
 
Back
Top