2 Formulas to read items

**jericho14444** · 09-15-2013, 11:28 AM

Hi,
I have a question for specific formulas. I have attached an example below of my spreadsheet.
I need a formula that would go into column F2, then autofill to the bottom. I need this formula to read Column B and E. If column B has 2 cash items at 0 days, column F3 would say cash+2 (F2 would be blank).

Second formula would go into column G2 and autofill to the bottom as well. It would read column E and it would count up total number of items based on the descriptions from Column F.

All the cells in between, I would prefer to have blank.
Is this possible? Thank you for any help that could be provided.

**L-Drr** · 09-15-2013, 11:46 AM

This is the formula for F2:

=IF(AND(B2=B3;E2=E3);"";B2&"+"&E2)

Maybe my semicolons should be commas for you.

Working on the next formula

**L-Drr** · 09-15-2013, 11:48 AM

This is for G2:

=IF(F2="";"";COUNTIFS($B$2:B2;B2;$E$2:E2;E2))

**mrice** · 09-15-2013, 11:57 AM

You can do this with user defined functions.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In F2, enter = Category(B2,E2)

In G2, enter = NumberOfItems(B2,E2)

and copy down.

Remember to save the workbook as a macro enabled workbook .xlsm

**jericho14444** · 09-15-2013, 12:59 PM

Guys, thank you sooo much! I really appreciate your help!

**jericho14444** · 09-22-2013, 12:30 PM

hello L-Drr, for some reason this formula =IF(AND(B2=B3;E2=E3);"";B2&"+"&E2) is coming up with all blanks for all cells. Yes, I had to use commas. Any idea why that would be? Thank you.

**L-Drr** · 09-22-2013, 12:40 PM

Nope, no idea...
Here's your file with my formula for column F (in column H), working correctly:

**jericho14444** · 10-19-2013, 11:15 AM

@L-Drr - I was wondering if you help me one more time with the formulas you created for this thread. Is is possible to tweak these formulas so they work when column A is filtered? For example, can you please take a look at the spreadsheet I attached? It shows the example. Thank you.

**L-Drr** · 10-19-2013, 04:07 PM

Hi jericho,

For as far as I understand, you can use the exact same formula...
See attachment

**jericho14444** · 10-20-2013, 02:04 PM

The issue here is this - if I apply this formula to the unfiltered list (Row 2 through 20) and then filter column A to "th42", the formulas do not work.

**L-Drr** · 10-22-2013, 11:15 AM

what if you only select columns A through E and then sort by A?

**jericho14444** · 10-22-2013, 01:19 PM

That still does not work. If I do that, all NUmber of items should be at "1" except one item which should be at "2".

**L-Drr** · 10-23-2013, 04:01 AM

Works fine for me...
Could you be more specific on what is going wrong? i.e. describe the wrong results that you are getting

**jericho14444** · 10-23-2013, 01:49 PM

Please take a look at column G on the spreadsheet you attached. G10, G14, G17, G18, and G18 have the wrong values. Thank you for all your help.

**L-Drr** · 10-24-2013, 03:09 AM

And what would be the right values, then? What is does now, is count the # of instances where the combination of B and E (in the same row) is also present in the rows above it.
Maybe you want it to keep the values that it had before filtering? In that case you'd have do cut column G and paste back as values before filtering.

**jericho14444** · 10-24-2013, 12:32 PM

This is a specific report and the values need to be there after it is filtered. The report is so large that this porcess takes forever.

I have attached a revised spreadsheet again.
1. I noticed there is a flaw with the first formula in column F. The formula needs to take into account that the ID's change. For example, if ID th49 changes to ID50, cell F22 is blank. It shouldn't be.
2. I think there needs to be a different formula in column P that also takes into account changing ID's.

Does anyone know how to do this? Thank you

**jericho14444** · 10-26-2013, 12:53 PM

Does anyone know how to do this? Thank you

2 Formulas to read items

LinkBack

Thread Tools

Rate This Thread

Display

2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Re: 2 Formulas to read items

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Read the top three items from a goods list

[SOLVED] Help! Formulas cannot be read by MS Excel 2002

Having Macro Read Values Not Formulas

Using ADO to read closed file does not read formulas

Need to read formulas

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions