Pulling data in Excel, need help

Sort by the card of interest in column 1 and then cut and paste the relevant data into a separate tab.

beythoven wrote:

Sort by the card of interest in column 1 and then cut and paste the relevant data into a separate tab.

No that won't work. The point is to automate it, i have one list of data, and 50 tabs for each individual card, from those tabs they'll feed into another sheet used as an upload. I need to pull into each of those tabs the unique data associated with that card, and i need it to show up in row 1,2,3,4,etc for every tab/card.

You could use if statements on each tab, referencing the first sheet-but by the time you set all that up, it would be faster to just cut and paste like the other guy said.

The old school way would be to write a basic program that sorted them out from a comma delineated file, and insert the tab name into a field that could be used as a pointer to the tab you want to insert to. Again, by the time you fool with all that, you'd be better off just cutting and pasting. It's only 50 tabs-what would that cut and paste take-15 minutes?

Sounds like you need to use a pivot table. Just google pivot table and you will find plenty of instruction.

It would also be a very easy vba code if you wanted more flexibility in automating it, but of course this requires more effort on your part so you can decide if you want to spend 30 minutes of your time learning very basic vba coding (easy to find tutorials and examples online).

Make a pivot table. You can then filter the data by pivoting by the card you want. If you want you can then copy/paste or otherwise extract that data to another blank worksheet. But the table will separate by card then sort and aggregate data however you tell it to.

If you want to open the data within a pivot field into a new tab you just double click on the number/field. So it's not automated but really a double click isn't too tasking to sort out the data one you build the table.

Agree with the rules wrote:

You could use if statements on each tab, referencing the first sheet-but by the time you set all that up, it would be faster to just cut and paste like the other guy said.

The old school way would be to write a basic program that sorted them out from a comma delineated file, and insert the tab name into a field that could be used as a pointer to the tab you want to insert to. Again, by the time you fool with all that, you'd be better off just cutting and pasting. It's only 50 tabs-what would that cut and paste take-15 minutes?

The point would be once this is set up, all i need to do once a month for the rest of time is paste in the entire data set into tab 1, and the other 50 tabs automatically populate with only the card data for that specific card, starting in row 1 and going down 1 row at a time until all the data for that card is listed.

Pivot tables wrote:

If you want to open the data within a pivot field into a new tab you just double click on the number/field. So it's not automated but really a double click isn't too tasking to sort out the data one you build the table.

It needs to be completely dumbified. No clicks, 1 master data tab with all card data, this will feed into 50 expense reports for each card that an automated email will go out to individuals with good to horrible excel skills. It has to use a formula of some sort with no work needed by the user.

I tried using a recorded macro but it kept freaking out because of merged cells (even though i didn't use any merged cells during the macro but there are some in the sheet). So i really just need a formula that will accomplish this.

Quit resisting. The answer is pivot tables.

What youre asking is for the limited functionality of ms excel to do database like queries on the data.

Put the card information into a database table via CSV. Then write some queries and save them. Using a basic programming language such as python you can even write a script to compile an excel sheet. Or you could take it one step further and generate tabular views. Maybe something as simple as flask, maybe something more sophisticated like django.

I just had a similar conversation with my younger brother. People rely on excel way too much.

malmo wrote:

Quit resisting. The answer is pivot tables.

Malmo it isn't for what I need. I'm not saying pivot tables can't do it , I'm saying it won't work in this scenario. The data is feeding directly into an expense form which is where the formula would go, it can't be a pivot table.

Some kind of advanced array maybe?

You should be able to automate it, but you'll have to write a macro in VBA. It's not all that difficult to get what you need, and it's possible that the code you need is already floating around out there. I have to create basically the same type of sort in a couple weeks for a project of mine, and reckoned it would take anywhere from a few minutes to a an hour to code correctly with my mediocre VBA skills.

As others have said, easiest route would be a pivot table. If you absolutely had to use a formula, you could do a "SUMIF(S)" on each tab for each card number. Though that would be a lot of unnecessary work -- just use pivot table instead (does the SUMIF for you).

Here is a pic: Take a look:

https://farm6.staticflickr.com/5555/14918981020_44aaedb72a_z.jpg

ding ding dingWinner Winner Chicken DinnerBuy a copy of Access-your task is a fifteen minute exercise.You don't even have to do a csv or python. Access will export to Excel exactly as you describe-if you're stuck on sending an excel expense report.You could get Access to print out a report that should be fine for your purposes.

the answer is wrote:

What youre asking is for the limited functionality of ms excel to do database like queries on the data.

Put the card information into a database table via CSV. Then write some queries and save them. Using a basic programming language such as python you can even write a script to compile an excel sheet. Or you could take it one step further and generate tabular views. Maybe something as simple as flask, maybe something more sophisticated like django.

I just had a similar conversation with my younger brother. People rely on excel way too much.

Relational DB wrote:

Buy a copy of Access-your task is a fifteen minute exercise.

You were doing well until this.

surely you just do a custom sort.

Sort by card number first, then add a new level for date (oldest to newest). It's not going to split them into separate sheets, but it'll at least give you the info you need in the order you need it. Can then just cut and paste the detail for each card into separate sheet (remembering obviously, that when you paste in formulated cells you need to do it as 'general' or 'number' or whatever)

King Z wrote:

surely you just do a custom sort.

Sort by card number first, then add a new level for date (oldest to newest). It's not going to split them into separate sheets, but it'll at least give you the info you need in the order you need it. Can then just cut and paste the detail for each card into separate sheet (remembering obviously, that when you paste in formulated cells you need to do it as 'general' or 'number' or whatever)

Needs to be seamless, 100 tabs possibly or more, all automated.

Got an excel guru of mine to sort it out. Will paste the formula later if anyone is interested.

I 100% understand many of your suggestions and they'd be perfect in some cases, just not in this case.

Excelhelp wrote:

Needs to be seamless, 100 tabs possibly or more, all automated.

I'm no math wiz, but from my calculations, you need 51 tabs (one master and one for each of the 50 cards). What are the other 49+ tabs for?

"How to Calculate Credit Card Payments in Excel"

http://www.wikihow.com/Calculate-Credit-Card-Payments-in-Excel

Alternatively you could Google

sample credit card spreadsheet every charge

or

how to record credit card credit card charge spreadsheet