Formatting exported DB data with IF(AND)?

[firefiend]

I have a spreadsheet that is sourced from a Database and I need to extract certain data from it and make it "pretty."

the tables are attached as a .png 'cause I can't enter the html and make it render.

but basically, I need either a function or some other snippet of code that will examine each row, determine the data in the B:B and D:D columns and output it accordingly.

I've tried the simple IF(AND()) functions but it only seems to look at the first row despite the range.

Anyone with more VBA experience have any ideas?

[Flintstone]

Hello Firefiend:

Assuming your data resides in columns A through D.

Paste this formula in cell F2 and copy down.

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)>1,"",COUNTIF(B$2:B$30,"<"&B2)+ROW()/100000))

Paste this formula in cell G2 and copy down.

=IF(ISERR(SMALL(F:F,ROW(1:1))),"",OFFSET(B$1,MID(SMALL(F:F,ROW(1:1)),FIND(".",SMALL(F:F,ROW(1:1))),6)*100000-1,0))

Paste this formula in cell H2 and copy down and across to cell J30, (adjust the range as needed).

=IF($G2="","",IF(SUMPRODUCT(--($B$2:$B$30=$G2),--($D$2:$D$30=COLUMN(A$1)))>0,"TRUE",""))

The two formulas in columns F and G will organize ITEMS in ascending order "<"; ie making it pretty. Hide column F.


Matt

[firefiend]

This seems to be doing the trick though the D:D data is actually a string and not numerical so I'm running a macro to transfer it it numerical data. It doesn't really affect the output since the headings provide the labeling data anyway.

I do have another question for you though. what would the script be for putting this into a macro?

I'm trying to make the process as automated as possible since I'm not the person who is going to be maintaining the project, I'm just designing and establishing it essentially.

Thanks again,

~Len