Excel

Clauric

All, I am wondering if any Boardies might be able to help with a problem I have.

I have 2 workbooks, and I need to check if a particular cell in column in WorkbookA is a match for a any cells column in WorkbookB. Easy so far (Vlookup will do the trick).

However, there is a catch. The cell (WA) being checked has to match not only for the date and description, but also the amount. And the columns in WB have multiple entries for both date and description.

I have tried using INDEX/MATCH but it keeps returning the wrong cell reference.

I've tried getting MATCH to return the date criteria which results in a value of 3456 (as an example). I then create two new cells with entries of ="B"&CellRef, and ="B"&CellRef+20. These work fine. However, when I try to enter these cells references into a second VLOOKUP, I get an error message.

Are there any other suggestions/tricks in doing a multi-criteria VLOOKUP using MATCH?

Thanks

Seth Brundle

Split out the values in WB so that each is unique and then do your lookup?

Clauric

kbannon wrote: »

Split out the values in WB so that each is unique and then do your lookup?

I'll give it a shot, but I was going for a more formulaic way of doing it, without having to manipulate the sheets first. The first column in WB has 40 unique values. Each of these values might might have 30 separate values in the next column.

Seth Brundle

Would something like this do it?
http://blog.contextures.com/archives/2012/10/09/find-text-with-index-and-match/

bnt

VLOOKUP works on a single-criterion only, so have you considered creating a single lookup field? In both WA and WB, concatenate the date, description and amount in to a single string, then do your lookup on that?

Seth Brundle

@bnt - my understanding of the issue is that WA is a list of single value fields whereas the fields in WB are both single and multi-valued.
If this is indeed the case, either splitting out the values in WB or something taken from the example link above might work.

Seth Brundle

Hang on, see the attached...
it uses a VLOOKUP using a wildcard
=IFERROR(VLOOKUP("*"&A2&"*",$B$2:$C$100,2,0),"")

Shiftworker

Hi all,

Is there a way to auto fill nine non-sequential numbers over & over in Excel?

example: 3,3,2,2,1,1,0,0,0

I really don't want to manually drag and fill these cells as there are thousands of rows...

Any help please, thank you.

Seth Brundle

(You should possibly start a new thread)

Will it always be in the same order e.g. 3,3,2,2,1,1,0,0,0,3,3,2,2,1,1,0,0,0,3,3,2,2,1,1,0,0,0... in which case use copy/paste within a macro.
Or will it be any random number between 0 and three (in which case use the RANDBETWEEN function)

Shiftworker

Thank you, it will always follow that sequence.
It's needed for a shift rota, will the copy and paste macro follow the correct sequence month by month?

Seth Brundle

I don't know what you mean month by month. I don't know what you are trying to achieve apart from having that pattern of numbers going down over and over.

One simple way to get the pattern 3,3,2,2,1,1,0,0,0 in the first nine rows and then again from rows 10 to 18 and again from 19 to 27, etc is to use the formula =A1 (or whatever is the cell reference for the first 3) - put that in cell A10 and =A2 for the second number 3 in cell A11 and so on. You could then fill down.

beauf

If it were me I'd write my own VBA macro to do it. Both the matching and the number generation.

Shiftworker

Thank you

mccy02

Hi


I have an excel problem and I hope someone can help me solve it. I have a Swift Excel file that I am uploading to Sharepoint, but it won't open in Sharepoint, and give me an error message saying that there are unsupported features, specifically:

"Custom toolbar attached to the workbook (also called attached toolbar)"

Can anyone tell me how I might resolve this in the file on my pc so that the uploaded file will open in Sharepoint please?

Thanks!

Seth Brundle

SharePoint does not support a lot of features within Excel, including VBA, add-ins and remote connections.
For your file to work, people will need to download the file and open it in a version of Excel that supports the custom toolbar, change the file and re-upload it.
https://docs.microsoft.com/en-us/sharepoint/dev/general-development/supported-and-unsupported-features

Calahonda52

I have two columns of ESB MPRNs which I need to compare to see what values are shared and what values are unique to both columns.

I have tried using Index Match but to no avail.
I did the first 200 yesterday using Ctrl C,F,V which worked but now I need to do another 400:o

Would be grateful for some guidance.
Thank you

Seth Brundle

If Column A is MPRN1 and Column B is MPRN 2 then in cell C2 do a Countif as follows...
=COUNTIF(B:B,A2)
This will tell you how many times the MPRN in A2 is in column B.
Fill this formula down through column c and use an excel autofilter to find those with a value of 0.

In column D use the formula
=COUNTIF(A:A,B2)
to get the MPRNs in cell B2 exists in column A.

Calahonda52

Seth Brundle wrote: »

If Column A is MPRN1 and Column B is MPRN 2 then in cell C2 do a Countif as follows...
=COUNTIF(B:B,A2)
This will tell you how many times the MPRN in A2 is in column B.
Fill this formula down through column c and use an excel autofilter to find those with a value of 0.

In column D use the formula
=COUNTIF(A:A,B2)
to get the MPRNs in cell B2 exists in column A.

Thank you very much for this:much appreciated:)
so 1 = a match and 0 no match

How can I count the number of 1's and zeros in the output columns?

Seth Brundle

You could use the COUNT function to count the numbers in the column (which gives you the total of both) and then use a SUM() to sum them up.
=SUM(C:C) will give you the total cells contaiing a 1
=COUNT(C:C) -SUM(C:C) will give you the number of zeroes.

I'd recommend looking at the likes of this: https://www.techonthenet.com/excel/

Calahonda52

Thanks again, will look at the tutorials.
If I want to extract the MPRNS that match/don't match, can this be done in excel or do I need to get something else working?

I have used reporting tools against large data bases before, am new to excel

Seth Brundle

Calahonda52 wrote: »

Thanks again, will look at the tutorials.
If I want to extract the MPRNS that match/don't match, can this be done in excel or do I need to get something else working?

I have used reporting tools against large data bases before, am new to excel

Excel has what is called Autofilter (Press Ctrl + Shift + L)
Filter for what you want and then delete the row?

https://support.office.com/en-ie/article/use-autofilter-to-filter-your-data-7d87d63e-ebd0-424b-8106-e2ab61133d92

The Tetrarch

Calahonda52 wrote: »

I have two columns of ESB MPRNs which I need to compare to see what values are shared and what values are unique to both columns.

I have tried using Index Match but to no avail.
I did the first 200 yesterday using Ctrl C,F,V which worked but now I need to do another 400:o

Would be grateful for some guidance.
Thank you

If you just want to SEE which values (MPRNs) are the same you could use conditional formatting.

You have two columns of cells, from cell B2 to cell C6 (B2:C6).

Position the cursor on cell B2
Go to the Excel Menu at the top: Conditional Formatting
Highlight Cell Rules
More Rules
Use a formula to determine which cell to format
Type in =COUNTIF($B$2:$C$6,B2)>1 .............. this will find the duplicates
Click Format and pick a colour to highlight the duplicated cells (yellow is good).
At this stage you only have cell B2 Conditionally Formatted.
To have all the cells from B2 to C6 (B2:C6) Conditionally Formatted, click Format Painter (small paintbrush in Menus top left),
Paint the cells B2 to C6 (this applies the Conditional Formatting to all those cells).
A quick way to paint all the cells is to Ctrl-Shift-down_arrow and then Ctrl-Shift-right_arrow.

You can apply a second Conditional Formatting to the same cell(s) using =COUNTIF($B$2:$C$6,B2)=1 and perhaps paint them light blue. ........ this will find the single values

You might need to experiment slightly. Sometimes one formula overrides the second, and you need to change the first formula to the second formula.

Notice the use of $ in the formulas.
You want to have the block C2:C6 as $C$2:$C$6 (i.e. unchanging) and the C2 as plain old C2.
(when in the formula pressing the F4 key cycles through from B2:C6 to $B$2:$C$6 and all variations in between)

Calahonda52

Thanks for this coding, will look at in the morning

namenotavailablE

Another method of using conditional formatting:

The Tetrarch

^ very good.
Although I have Office 365 Excel I still use Excel 2003.